﻿Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports System.Linq
Imports System.Net
Imports System.Web

Public Class RbContainerBusiness
    Private m_CommonObj = New uLMSCommonBusiness()
    Private m_ContainerObj = New uLMSContainerBusiness()
    Private m_DocViewObj = New uLMSDocumetViewBusiness()
    Private ReadOnly lockObj As New Object()
    Private rbFactoryID = System.Configuration.ConfigurationManager.AppSettings("RBFactoryID").ToString() '热表ID
    Private mesDocPath = System.Configuration.ConfigurationManager.AppSettings("MesDocPath").ToString() '附件地址
    Private toAppID_RB = System.Configuration.ConfigurationManager.AppSettings("ToAppID_RB").ToString() '推送给数据中心目标地址（工控网）
#Region "热表批次模板上传"
    ''' <summary>
    ''' 获取热表分厂流水卡的信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRbContainerInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strsql As New StringBuilder
        Dim strsqlvalue As String =
            <string>SELECT * FROM RBContainertemplate r  WHERE 1=1 </string>.Value
        strsql.AppendLine(strsqlvalue)
        '查询
        If para.Keys.Contains("productNo") Then
            strsql.AppendFormat(" AND r.productname LIKE '%{0}%'", para("productNo"))
        End If
        If para.Keys.Contains("stepNo") Then
            strsql.AppendFormat(" AND r.specno LIKE '%{0}%'", para("stepNo"))
        End If

        'add:Wangjh 20211125
        If para.Keys.Contains("SpecNos") Then
            If Not String.IsNullOrWhiteSpace(para("SpecNos")) Then
                strsql.AppendFormat(" AND r.specno in ({0})", para("SpecNos"))
            End If
        End If

        'add:Wangjh 20211125
        If para.Keys.Contains("ProductNos") Then
            If Not String.IsNullOrWhiteSpace(para("ProductNos")) Then
                strsql.AppendFormat(" AND r.Productname in ({0})", para("ProductNos"))
            End If
        End If

        strsql.AppendLine("ORDER BY  r.createdate DESC")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("pageNumber").ToString)
        paraPage.PageSize = Convert.ToInt32(para("pageSize").ToString)
        paraPage.strSQL = strsql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 添加热表流水卡信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveExcelData(ByVal para As List(Of Dictionary(Of String, String))) As Boolean
        SaveExcelData = True
        For Each item As Dictionary(Of String, String) In para
            Dim strsql As New StringBuilder
            strsql.Clear()
            If String.IsNullOrWhiteSpace(item("RBContainerTemplateId")) Then
                '判断此产品，此工序号，此附件是否已经存在
                Dim selectSql = "select 1 from RBContainertemplate where ProductName='{0}' and SpecNo='{1}' and FileName='{2}'"
                If DatabaseHelper.GetSingle(String.Format(selectSql, item("ProductName"), item("SpecNo"), item("FileName"))) = Nothing Then
                    strsql.AppendLine("INSERT INTO RBContainertemplate (RBContainertemplateid,productname,SpecNo,FileName,")
                    strsql.AppendLine("FilePath,CreateDate,CreateEmployeeId) VALUES(")
                    strsql.AppendFormat("'{0}',", Guid.NewGuid.ToString)
                    strsql.AppendFormat("'{0}',", item("ProductName"))
                    strsql.AppendFormat("'{0}',", item("SpecNo"))
                    strsql.AppendFormat("'{0}',", item("FileName"))
                    strsql.AppendFormat("'{0}',", item("FilePath"))
                    strsql.AppendFormat("{0},", "sysdate")
                    strsql.AppendFormat("'{0}')", item("CreateEmployeeId"))
                    DatabaseHelper.ExecuteSql(strsql.ToString())
                End If
            Else
                strsql.AppendLine("UPDATE RBContainertemplate SET ")
                strsql.AppendFormat("productname='{0}', ", item("ProductName"))
                strsql.AppendFormat("SpecNo='{0}', ", item("SpecNo"))
                strsql.AppendFormat("FileName='{0}', ", item("FileName"))
                strsql.AppendFormat("FilePath='{0}' ", item("FilePath"))
                strsql.AppendFormat(" where RBContainertemplateid='{0}'", item("RBContainerTemplateId"))
                DatabaseHelper.ExecuteSql(strsql.ToString())
            End If
        Next

    End Function

    ''' <summary>
    ''' 删除热表流水卡信息
    ''' </summary>
    ''' <param name="rBContainertemplateId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteRbContainerById(ByVal rBContainertemplateId As String) As Boolean
        DeleteRbContainerById = False
        Dim strSql = ""
        strSql = String.Format("DELETE FROM RBContainertemplate r WHERE r.RBContainertemplateid = '{0}' ", rBContainertemplateId)
        Dim row As Integer = DatabaseHelper.ExecuteSql(strSql.ToString())
        If row > 0 Then
            DeleteRbContainerById = True
        End If
    End Function

    ''' <summary>
    ''' 获取批次模板列表
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerTemList(ByVal filename As String) As DataTable
        Dim strsql As New StringBuilder
        Dim strsqlvalue As String =
            <string>SELECT * FROM rbcontainertemplatelist where 1=1 </string>.Value
        If Not String.IsNullOrWhiteSpace(filename) Then
            strsqlvalue += " and FILENAME='" + filename + "' "
        End If
        strsql.AppendLine(strsqlvalue)

        GetContainerTemList = DatabaseHelper.Query_ReturnDataSet(strsql.ToString()).Tables(0)
    End Function
#End Region

#Region "热表批次创建"
    ''' <summary>
    ''' 获取批次的子批次信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSubContainer(ByVal para As Dictionary(Of String, String)) As DataTable

        Dim strSql = <string>select Distinct ws.workflowstepid,ws.workflowstepname,c.containername,c.qty,c.containerid,c.RbContainerType,c.RbTogetherflag
 from containersubcontainer cc
left join workflowstep ws on ws.workflowstepid=cc.workflowstepid
left join containercurrentinfo fc on fc.containerid=cc.ContainerID--父批次
left join container c on c.containerid=cc.subcontainerid --子批次
where 1=1 and c.status!=0 </string>.Value

        If para.ContainsKey("ContainerID") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerID")) Then
                strSql += String.Format(" and cc.containerid='{0}' ", para("ContainerID"))
            End If
        End If

        If para.ContainsKey("ContainerIDs") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerIDs")) Then
                strSql += String.Format(" and cc.containerid in ({0}) ", para("ContainerIDs"))
            End If
        End If

        If para.ContainsKey("SubContainerFactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("SubContainerFactoryID")) Then
                strSql += String.Format(" and c.originalfactoryid='{0}' ", para("SubContainerFactoryID"))
            End If
        End If

        If para.ContainsKey("SubContainerID") Then
            If Not String.IsNullOrWhiteSpace(para("SubContainerID")) Then
                strSql += String.Format(" and c.containerid='{0}' ", para("SubContainerID"))
            End If
        End If

        If para.ContainsKey("WorkflowStepID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowStepID")) Then
                strSql += String.Format(" and cc.WorkflowStepID='{0}' ", para("WorkflowStepID"))
            End If
        End If

        If para.ContainsKey("FaTogetherFlag") Then
            If Not String.IsNullOrWhiteSpace(para("FaTogetherFlag")) Then
                strSql += String.Format(" and fc.TogetherFlag='{0}' ", para("FaTogetherFlag"))
            End If
        End If

        If para.ContainsKey("FaTogetherFlags") Then
            If Not String.IsNullOrWhiteSpace(para("FaTogetherFlags")) Then
                strSql += String.Format(" and fc.TogetherFlag in ({0}) ", para("FaTogetherFlags"))
            End If
        End If

        If para.ContainsKey("SubRbContainerType") Then
            If Not String.IsNullOrWhiteSpace(para("SubRbContainerType")) Then
                strSql += String.Format(" and c.RbContainerType='{0}' ", para("SubRbContainerType"))
            End If
        End If

        strSql += " order by c.containerid desc "

        Return DatabaseHelper.Query_ReturnDatTable(strSql)


    End Function

    ''' <summary>
    ''' 获取最新的热表大批次号顺序
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewestRbTogetherflag(ByVal prefixInfo As String) As Integer
        Dim strSql = <string>select max( TO_NUMBER(REPLACE(c.RbTogetherflag,'{0}','')) ) maxRbTogetherflag from container  c 
where c.RbTogetherflag like '{0}%' and c.status!=0 and c.parentcontainerid is null and c.splitfromid is null </string>.Value

        Dim m = DatabaseHelper.GetSingle(String.Format(strSql, prefixInfo))
        If m Is Nothing Then
            Return 0
        Else
            Dim maxIndex = m.ToString().Replace(prefixInfo, "")
            If IsNumeric(maxIndex) Then
                Return maxIndex
            Else
                Return 0
            End If
        End If

    End Function

    ''' <summary>
    ''' 热表批次号生成
    ''' </summary>
    ''' <param name="prefixInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewestContainerNo(ByVal prefixInfo As String) As Integer
        Dim strSql = <string>select max(c.containername) maxcontainername from container  c where c.containername like '{0}%' and c.status!=0 and c.parentcontainerid is null and c.splitfromid is null 
                            AND NOT EXISTS(SELECT 1 FROM userattribute u WHERE u.parentid = c.containerid AND u.userattributename = '返修批次工步')
                     </string>.Value

        Dim m = DatabaseHelper.GetSingle(String.Format(strSql, prefixInfo))
        If m Is Nothing Then
            Return 0
        Else
            Dim maxIndex = m.ToString().Replace(prefixInfo, "")
            If IsNumeric(maxIndex) Then
                Return maxIndex
            Else
                Return 0
            End If
        End If
        'Dim strSql = <string>select max(c.containername) maxcontainername from container  c where (c.containername like '{0}%'{1}) and c.status!=0 and c.parentcontainerid is null and c.splitfromid is null </string>.Value
        'Dim strfarbtogSql = String.Empty
        'Dim strfapre = String.Empty
        'If prefixInfo.Contains("子工艺") Then
        '    strfapre = prefixInfo.Substring(0, prefixInfo.IndexOf("子")) + "/"
        '    strfarbtogSql = String.Format(" or c.containername like '{0}%'", strfapre + rbTogetherflag + "-")
        'End If

        'Dim m = DatabaseHelper.GetSingle(String.Format(strSql, prefixInfo + rbTogetherflag + "-", strfarbtogSql))
        'If m Is Nothing Then
        '    Return 0
        'Else
        '    Dim maxIndex = 0
        '    If Not m.ToString().Contains(prefixInfo) Then
        '        maxIndex = m.ToString().Replace(strfapre + rbTogetherflag + "-", "")
        '    Else
        '        maxIndex = m.ToString().Replace(prefixInfo + rbTogetherflag + "-", "")
        '    End If
        '    If IsNumeric(maxIndex) Then
        '        Return maxIndex
        '    Else
        '        Return 0
        '    End If
        'End If
    End Function

    ''' <summary>
    ''' 获取批次的机型信息
    ''' </summary>
    ''' <param name="containerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerProductFamily(ByVal containerId As String) As DataTable
        Dim strSql = <string>select pf.productfamilyname,pf.productfamilyid,wf.containerid,wf.sequence from containerproductfamilylist wf
            left join productfamily pf on pf.productfamilyid=wf.productfamilylistid
             where wf.containerid='{0}' </string>.Value
        strSql = String.Format(strSql, containerId)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 填充批次默认信息
    ''' </summary>
    ''' <param name="containerBaseInfo"></param>
    ''' <remarks></remarks>
    Public Sub FillContainerDefaultInfo(ByRef containerBaseInfo As ContainerStartInfo, ByVal factoryid As String)
        Dim strFactoryID As String = factoryid
        Dim levelAttributes As DataTable = m_CommonObj.GetFatoryAttributes(strFactoryID)
        Dim datarows() As DataRow = levelAttributes.Select("USERATTRIBUTENAME='默认所有者'")
        Dim strOwner As String = ""
        If datarows.Length > 0 Then
            strOwner = datarows.First.Item("ATTRIBUTEVALUE")
        End If

        datarows = levelAttributes.Select("USERATTRIBUTENAME='默认层级'")
        Dim strLevel As String = ""
        If datarows.Length > 0 Then
            strLevel = datarows.First.Item("ATTRIBUTEVALUE")
        End If

        Dim strStart As String = ""
        If String.IsNullOrWhiteSpace(containerBaseInfo.MfgOrder) Then
            datarows = levelAttributes.Select("USERATTRIBUTENAME='无订单开始原因'")
        Else
            datarows = levelAttributes.Select("USERATTRIBUTENAME='订单开始原因'")
        End If

        If datarows.Length > 0 Then
            strStart = datarows.First.Item("ATTRIBUTEVALUE")
        End If

        containerBaseInfo.StartReason = strStart
        containerBaseInfo.Level = strLevel
        containerBaseInfo.Owner = strOwner
        'If containerBaseInfo.IsSelfMfg = "1" Then
        '    datarows = levelAttributes.Select("USERATTRIBUTENAME='自投料流程'")
        'Else
        '    datarows = levelAttributes.Select("USERATTRIBUTENAME='领料流程'")
        'End If
        'If datarows.Length > 0 Then
        '    Dim strWorkflows() = datarows(0)("ATTRIBUTEVALUE").ToString.Split("：")
        '    containerBaseInfo.WorkflowName = strWorkflows.First
        '    containerBaseInfo.WorkflowRevision = strWorkflows.Last
        '    containerBaseInfo.WorkflowuseROR = False
        'End If

    End Sub

    ''' <summary>
    ''' 根据ID获取转工工序信息
    ''' </summary>
    ''' <param name="shipID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetShipStepsByID(ByVal shipID As String) As DataTable
        Dim strSql = <string>select ws.workflowstepid,ws.workflowstepname,cr.containershiprecordid from containershiprecordstep cr
left join workflowstep ws on ws.workflowstepid=cr.workflowstepid
where cr.containershiprecordid='{0}'
order by ws.sequence asc</string>.Value

        strSql = String.Format(strSql, shipID)

        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    ''' <summary>
    ''' 热表根据机加的批次开卡，机加批次开卡时可能它的子或父已经开过热表卡了，查询机加togetherflag的子父togetherflag
    ''' </summary>
    ''' <param name="faTogetherflag"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRelativesFatogetherflag(ByVal faTogetherflag As String) As DataTable
        Dim result As New DataTable
        result.Columns.Add("FATOGETHERFLAG")
        Dim row As DataRow = result.NewRow
        row("FATOGETHERFLAG") = faTogetherflag
        result.Rows.Add(row)

        '查询子
        Dim strSql = <String>select distinct cci2.togetherflag faTogetherflag from containersubcontainer cc
left join containercurrentinfo cci on cci.containerid=cc.containerid--父
inner join container cci2 on cci2.containerid=cc.subcontainerid and cci2.status!=0  and cci2.rbcontainertype is null--子
where cci.togetherflag='{0}'</String>.Value

        Dim dt = DatabaseHelper.Query_ReturnDatTable(String.Format(String.Format(strSql, faTogetherflag)))

        If dt.Rows.Count > 0 Then
            result.Merge(dt)
        End If

        dt.Rows.Clear()

        '查询父
        strSql = <String>select distinct cci.togetherflag faTogetherflag from containersubcontainer cc
left join containercurrentinfo cci on cci.containerid=cc.containerid--父
left join container cci2 on cci2.containerid=cc.subcontainerid and cci2.status!=0 and cci2.rbcontainertype is null--子
where cci2.togetherflag='{0}'</String>.Value
        dt = DatabaseHelper.Query_ReturnDatTable(String.Format(String.Format(strSql, faTogetherflag)))
        If dt.Rows.Count > 0 Then
            result.Merge(dt)
        End If

        Return result

    End Function

    ''' <summary>
    ''' 根据父批次ID获取有效的子序号批次 add by zhangrj 2022-10-25
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowstepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetChildContainer(ByVal containerID As String, ByVal workflowstepID As String) As DataTable
        Dim strSql = <string>
SELECT c.containerid,c.containername,c.qualificationnum FROM container c 
WHERE c.parentcontainerid = '{0}' AND c.status =1 AND c.containerid NOT IN 
(SELECT csc.childcontainerid FROM containersubcontainer csc 
LEFT JOIN container c1 ON c1.containerid = csc.subcontainerid 
WHERE csc.containerid = '{0}' AND csc.workflowstepid = '{1}' AND c1.status = 1 AND csc.childcontainerid IS NOT NULL)
ORDER BY c.containerid                         
                     </string>.Value
        strSql = String.Format(strSql, containerID, workflowstepID)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
#End Region

#Region "热表流水卡打印"
    ''' <summary>
    ''' 获取打印相关信息
    ''' add:zhangrj 20211018 
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerPrintInfo(ByVal para As Dictionary(Of String, Object)) As DataSet
        Dim result As DataSet = New DataSet()
        Dim containerId As String = para("ContainerId"), workflowId = para("WorkflowId")
        Dim strSql = String.Format("SELECT 1 FROM containersubcontainer csc WHERE csc.subcontainerid = '{0}'", containerId)
        Dim dtMain As New DataTable
        Dim isSubContainer = True '流卡是否是子卡（默认是）
        If Not DatabaseHelper.GetSingle(strSql) Is Nothing Then '如果批次是子卡
            dtMain = GetSubContainerPrintData(containerId, workflowId)
        Else '是热表自己的无订单批次
            dtMain = GetSelfContainerPrintData(containerId, workflowId)
            isSubContainer = False
        End If

        dtMain.TableName = "dtMain"
        If dtMain.Rows.Count = 0 Then
            Return result
        End If

        strSql = String.Format("SELECT SUM(NVL(cci.lossqty,0)) lossqty FROM containercheckinfo2 cci WHERE cci.containerid = '{0}' AND cci.isuse = 0 GROUP BY cci.containerid", containerId) '查出批次所有的报废数
        Dim dtLossQty = DatabaseHelper.Query_ReturnDatTable(strSql)

        If dtLossQty.Rows.Count > 0 Then
            dtMain.Rows(0)("originalqty") = Integer.Parse(dtMain.Rows(0)("qty").ToString()) + Integer.Parse(dtLossQty.Rows(0)("lossqty").ToString())
        End If


        Dim faContainerId = ""
        If isSubContainer Then
            '此时热表的父卡可能也是机加分厂的子卡，product上的信息需要用最上层的 add by wangjh 20211018
            strSql = <string>select nvl(p.workflowroute,pb.productname) faproductname,pb.productname orgfaproductname,p.description faproductdesc,p.materialtrademark faMaterialtrademark,p.MaterialSpecification faMaterialSpecification from containercurrentinfo cci
                            left join containersubcontainer cc on cc.containerid=cci.containerid
                            left join product p on p.productid=cci.productid
                            left join productbase pb on pb.productbaseid=p.productbaseid
                            where cc.subcontainerid='{0}'</string>.Value
            For Each row As DataRow In dtMain.Rows
                Dim topFaInfo = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, row("facontainerid").ToString()))
                If topFaInfo.Rows.Count > 0 Then
                    row("faproductname") = topFaInfo.Rows(0)("faproductname")
                    row("faproductdesc") = topFaInfo.Rows(0)("faproductdesc")
                    row("faMaterialtrademark") = topFaInfo.Rows(0)("faMaterialtrademark")
                    row("faMaterialSpecification") = topFaInfo.Rows(0)("faMaterialSpecification")
                    row("orgfaproductname") = topFaInfo.Rows(0)("orgfaproductname")
                End If
                faContainerId += "'" + row("facontainerid").ToString() + "',"
            Next
        End If
        dtMain.AcceptChanges()
        If Not String.IsNullOrWhiteSpace(faContainerId) Then
            faContainerId = faContainerId.Trim(",")
        Else
            faContainerId = "''"
        End If

        '取父卡批次属性
        strSql = String.Format("select c.batchcomments,ut.* from userattribute ut LEFT JOIN container c ON c.containerid=ut.parentid where ut.parentid in ({0})", faContainerId)
        Dim faContainerAttrDt = DatabaseHelper.Query_ReturnDatTable(strSql)
        faContainerAttrDt.TableName = "faContainerAttrDt"

        '获取工步 add by zhangrj 20220816
        strSql = <string>
                    --最顶层查出批次的所有工步，并左连接第二层查出的派报检数据，得到工步的派报检数据，
                    SELECT cc.description stepinfoname,cc.stepno,cc.stepinfoid,re.reportdate,re.checkqty,re.acceptanceqty2,re.acceptanceqty,
                    re.lossqty,re.reworkqty,re.defectqty,re.checkername,re.workername,re.workerid,re.checkerid
                    FROM (
                    SELECT Distinct s.stepinfoid,ws.workflowstepid,c.containerid,ws.sequence,s.stepno,s.stepseq,s.description 
                    FROM containercurrentinfo c 
                    LEFT JOIN container cc ON cc.containerid = c.containerid
                    LEFT JOIN Workflow w ON w.workflowid = DECODE(c.isinstore,-1,cc.mfgworkflowid,0,c.workflowid,c.bsworkflowid)
                    LEFT JOIN workflowstep ws ON ws.workflowid = w.workflowid
                    LEFT JOIN Userattribute u ON u.parentid = c.containerid AND u.userattributename = '开始工步顺序号'
                    LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid AND s.stepseq >= NVL(u.attributevalue,0)
                    LEFT JOIN containerstepdispatch2 csd ON csd.containerid = c.containerid AND csd.workflowstepid = ws.workflowstepid AND csd.isuse = 0
                    LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd.stepinfoid
                    LEFT JOIN stepinfobase sb ON sb.revofrcdid = s.Stepinfoid --取最新版
                    LEFT JOIN stepinfo s2 ON s2.stepinfoid = sb.revofrcdid
                    WHERE c.containerid = '{0}' AND s.stepinfoid IS NOT NULL
                    AND (CASE WHEN csd.containerstepdispatchid IS NULL THEN s2.revision ELSE s1.Revision END) = s.revision
                    ) cc
                    LEFT JOIN (
                    --第二层分组查出的派报检数据，并合计每个工步的合格数等
                     SELECT k.containerid,k.workflowstepid,k.stepinfoid,MAX(k.createdate) reportdate,SUM(k.checkqty) checkqty,
                     SUM(k.acceptanceqty2) acceptanceqty2,SUM(k.acceptanceqty) acceptanceqty,SUM(k.lossqty) lossqty,SUM(k.reworkqty) reworkqty,SUM(k.defectqty) defectqty,
                     k.checkername,k.workername,k.workerid,k.checkerid
                     FROM (
                             SELECT Distinct --第一层查出所有的工步派报检数据，并去重
                             csd.containerid,csd.workflowstepid,csd.stepinfoid,csw.createdate,nvl(cci.checkqty,0) checkqty,
                             nvl(cci.acceptanceqty2,0) acceptanceqty2,nvl(cci.acceptanceqty,0) acceptanceqty,nvl(cci.lossqty,0) lossqty,nvl(cci.reworkqty,0) reworkqty,nvl(cci.defectqty,0) defectqty,
                             e1.employeeid checkerid,e1.fullname checkername,e.employeeid workerid,e.fullname workername
                              FROM containerstepdispatch2 csd 
                              LEFT JOIN containerstepworkreport2 csw ON csw.containerid = csd.containerid AND csw.workflowstepid = csd.workflowstepid 
                                                                     AND csw.stepinfoid = csd.stepinfoid AND csw.isuse = 0
                              LEFT JOIN employee e ON e.employeeid = csw.workerid
                              LEFT JOIN containercheckinfo2 cci ON cci.containerstepworkreportid = csw.containerstepworkreportid  AND cci.isuse = 0
                              LEFT JOIN employee e1 ON e1.employeeid = cci.checkerid
                              WHERE csd.isuse = 0 
                     )k 
                     GROUP BY k.containerid,k.workflowstepid,k.stepinfoid,k.checkerid,k.checkername,k.workerid,k.workername
                    )re ON re.stepinfoid = cc.stepinfoid AND re.containerid = cc.containerid AND re.workflowstepid = cc.workflowstepid

                    ORDER BY cc.sequence, to_number(cc.stepseq) asc
                  </string>.Value
        strSql = String.Format(strSql, containerId)
        Dim dtStepInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtStepInfo.TableName = "dtStepInfo"

        '工步备注（工人备注，检验备注） add by zhangrj 20220816
        strSql = <string>
                      SELECT Distinct
                      csw.containerid,csw.stepinfoid,csw.notes,cci.checknote,e.employeeid workerid,e1.employeeid checkerid
                      FROM containerstepworkreport2 csw
                      LEFT JOIN employee e ON e.employeeid = csw.workerid
                      LEFT JOIN containercheckinfo2 cci ON cci.containerstepworkreportid = csw.containerstepworkreportid  AND cci.isuse = 0
                      LEFT JOIN employee e1 ON e1.employeeid = cci.checkerid
                      WHERE csw.isuse = 0
                      AND csw.containerid = '{0}'
                 </string>
        strSql = String.Format(strSql, containerId)
        Dim dtStepInfoNotes = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtStepInfoNotes.TableName = "dtStepInfoNotes"

        '工步检测项 add by zhangrj 20220817
        strSql = <string>
                    SELECT s.stepinfoid,s.stepno,s.description stepinfoname,sp.checkpointnote,sp.checkpointnotedis,sp.checkpointnotedis_b,
                    sp.drawingrequiredis,sp.drawingrequiredis_b,sp.drawingrequire, e.fullname workername,e1.fullname checkername,
                    spr.checkpointresultnote,spr.checkpointresultnotedis,spr.checkpointresultnotedis_b,
                    spr.workcheckpointresultnote,spr.workcheckpointresultnotedis,spr.workcheckpointresultnotedis_b
                    FROM containercurrentinfo c
                    LEFT JOIN container cc ON cc.containerid = c.containerid
                    LEFT JOIN Workflow w ON w.workflowid = DECODE(c.isinstore,-1,cc.mfgworkflowid,0,c.workflowid,c.bsworkflowid)
                    LEFT JOIN workflowstep ws ON ws.workflowid = w.workflowid
                    LEFT JOIN Userattribute u ON u.parentid = c.containerid AND u.userattributename = '开始工步顺序号'
                    LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid AND s.stepseq >= NVL(u.attributevalue,0)
                    LEFT JOIN stepinfobase sb ON sb.stepinfobaseid = s.stepinfobaseid
                    LEFT JOIN stepcheckpoint sp ON sp.stepinfoid = s.stepinfoid AND sp.workflowstepid = ws.workflowstepid
                    LEFT JOIN stepcheckpointresult spr ON spr.stepcheckpointid = sp.stepcheckpointid AND spr.containerid = c.containerid
                    LEFT JOIN employee e ON e.employeeid = spr.workerid
                    LEFT JOIN employee e1 ON e1.employeeid = spr.checkerid
                    WHERE c.containerid = '{0}' AND sp.stepcheckpointid IS NOT NULL
                    ORDER BY ws.sequence, to_number(s.stepseq) asc
                 </string>
        strSql = String.Format(strSql, containerId)
        Dim dtStepInfoCheckItem = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtStepInfoCheckItem.TableName = "dtStepInfoCheckItem"

        result.Tables.Add(dtMain) '主数据
        result.Tables.Add(faContainerAttrDt) '批次属性
        result.Tables.Add(dtStepInfo) '工步
        result.Tables.Add(dtStepInfoNotes) '工步备注
        result.Tables.Add(dtStepInfoCheckItem) '工步检测项

        Return result
    End Function

    ''' <summary>
    ''' 获取子卡批次信息（打印）
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetSubContainerPrintData(ByVal containerID As String, ByVal workflowID As String) As DataTable
        Dim strSql = <string>
                         select Distinct e.fullname startname,m.mfgordername,m.lgort,c.containername,c.rbtogetherflag,w.process_no,to_char(c.originalstartdate,'yyyy-mm-dd') originalstartdate,cci.containerid facontainerid,cci.containername facontainername,
                        cci.togetherflag fatogetherflag,fa.factoryname fafactoryname,nvl(cci.workflowroute,cci.productname) faproductname,cci.productname orgfaproductname,cci.containertype facontainertype,p.charactertype,
                        cci.productdescription faproductdesc,replace(c.mfgbatch,'型别：','') mfgbatch,cci.containerno facontainerno,cci.RealContainerNo FARealContainerNo,cc.faqty,c.qty,p.Materialtrademark faMaterialtrademark,
                        p.MaterialSpecification faMaterialSpecification,s.specno faspecno,s.opdesc faopdesc,'' specialcoursespecreportno,c.containerid,'' faprocess_no,c.containercomments,c.originalqty,
                        NVL2(s1.specid,'true','false') isThreeWorkflow,'' isspotcheck, '' iskey
                        from container c
                        left join containersubcontainer cc on cc.subcontainerid=c.containerid--父子映射信息
                        left join employee e on e.employeeid=c.startemployeeid
                        left join mfgorder m on m.mfgorderid=c.mfgorderid

                        left join containercurrentinfo cci on cci.containerid=cc.containerid--父卡信息
                        left join factory fa on fa.factoryid=cci.originalfactoryid
                        left join product p on p.productid=cci.productid
                        left join workflowstep ws on ws.workflowstepid=cc.workflowstepid--子卡创建时，父卡的工序状态
                        left join specbase sb on sb.specbaseid=ws.specbaseid
                        left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)

                        LEFT JOIN containercurrentinfo cti ON cti.containerid = c.containerid --子卡（本卡）信息
                        left join workflow w on w.workflowid='{0}'
                        left join workflowbase wb on wb.workflowbaseid = w.workflowbaseid
                        LEFT JOIN spec s1 ON s1.commonsubworkflow = replace(wb.workflowname,'WF-','')
                        where nvl(c.rbtogetherflag,'0')!='0' and c.status!=0 and c.containerid = '{1}'
                         </string>.Value
        strSql = String.Format(strSql, workflowID, containerID)
        Dim dtMain = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim isThreeWorkflow = Boolean.Parse(dtMain.Rows(0)("isThreeWorkflow").ToString())
        If isThreeWorkflow Then '如果是三层工艺，取第二层数据展示
            strSql = <string>
                            SELECT ssr.specialcoursespecreportno,ww.process_no,s.isspotcheck,s.iskey FROM containersubcontainer cc 
                            LEFT JOIN stepsubstep sbs ON sbs.workflowstepid = cc.workflowstepid 
                            LEFT JOIN workflow ww ON ww.workflowid = sbs.sonworkflowid
                            LEFT JOIN workflowstep wws on wws.workflowstepid = sbs.sonworkflowstepid
                            LEFT JOIN specbase sb on sb.specbaseid=wws.specbaseid
                            LEFT JOIN spec s on s.specid=nvl(sb.revofrcdid,wws.specid)
                            LEFT JOIN SpecialCourseSpecMapp scs on scs.workflowstepid = wws.workflowstepid
                            LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid --AND ssr.indate > SYSDATE
                            WHERE cc.subcontainerid= '{0}'
                     </string>.Value
            Dim dtThree = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerID))
            dtMain.Rows(0)("specialcoursespecreportno") = dtThree.Rows(0)("specialcoursespecreportno").ToString()
            dtMain.Rows(0)("faprocess_no") = dtThree.Rows(0)("process_no").ToString()
            dtMain.Rows(0)("isspotcheck") = dtThree.Rows(0)("isspotcheck").ToString()
            dtMain.Rows(0)("iskey") = dtThree.Rows(0)("iskey").ToString()
        Else
            strSql = <string>
                            SELECT ssr.specialcoursespecreportno,s.isspotcheck,s.iskey
                            FROM  workflow w 
                            LEFT JOIN workflowstep ws ON ws.workflowid = w.workflowid
                            LEFT JOIN specbase sb on sb.specbaseid=ws.specbaseid
                            LEFT JOIN spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                            LEFT JOIN SpecialCourseSpecMapp scs ON scs.workflowid = w.workflowid
                            LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid --AND ssr.indate > SYSDATE
                            WHERE w.workflowid = '{0}' 
                     </string>.Value
            Dim dtTwo = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, workflowID))
            dtMain.Rows(0)("specialcoursespecreportno") = dtTwo.Rows(0)("specialcoursespecreportno").ToString()
            dtMain.Rows(0)("isspotcheck") = dtTwo.Rows(0)("isspotcheck").ToString()
            dtMain.Rows(0)("iskey") = dtTwo.Rows(0)("iskey").ToString()
        End If
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取无订单批次信息（打印）
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetSelfContainerPrintData(ByVal containerID As String, ByVal workflowID As String) As DataTable
        Dim strSql = <string>
                        select Distinct e.fullname startname,m.mfgordername,m.lgort,c.containername,c.rbtogetherflag,w.process_no,to_char(c.originalstartdate,'yyyy-mm-dd') originalstartdate,cci.containerid facontainerid,cci.containername facontainername,
                        cci.togetherflag fatogetherflag,u.attributevalue fafactoryname,nvl(cci.workflowroute,cci.productname) faproductname,cci.productname orgfaproductname,cci.containertype facontainertype,p.charactertype,
                        cci.productdescription faproductdesc,replace(c.mfgbatch,'型别：','') mfgbatch,u1.attributevalue facontainerno,u1.attributevalue FARealContainerNo,cci.qty faqty,c.qty,p.Materialtrademark faMaterialtrademark,
                        p.MaterialSpecification faMaterialSpecification,s.specno faspecno,s.opdesc faopdesc,ssr.specialcoursespecreportno,c.containerid,ww.process_no faprocess_no,c.containercomments,c.originalqty,s.isspotcheck,s.iskey
                        from container c
                        left join employee e on e.employeeid=c.startemployeeid
                        left join mfgorder m on m.mfgorderid=c.mfgorderid
                        left join workflow w on w.workflowid='{0}'
                        LEFT JOIN Workflowstep ws1 ON ws1.workflowid = w.workflowid
                        left join containercurrentinfo cci on cci.containerid=c.containerid
                        LEFT JOIN Userattribute u ON u.parentid = c.containerid AND u.userattributename = '委托单位'
                        LEFT JOIN Userattribute u1 ON u1.parentid = c.containerid AND u1.userattributename = '委托批次'
                        LEFT JOIN Userattribute u2 ON u2.parentid = c.containerid AND u2.userattributename = '无订单开卡父工序ID'
                        LEFT JOIN Userattribute u3 ON u3.parentid = c.containerid AND u3.userattributename = '无订单开卡父工艺ID'
                        LEFT JOIN workflow ww ON ww.workflowid = u3.attributevalue
                        left join factory fa on fa.factoryid=cci.originalfactoryid
                        left join product p on p.productid=cci.productid
                        left join workflowstep ws on ws.workflowstepid=NVL(u2.attributevalue,ws1.workflowstepid)
                        left join specbase sb on sb.specbaseid=ws.specbaseid
                        left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                        LEFT JOIN SpecialCourseSpecMapp scs ON scs.workflowid = NVL(ww.workflowid,w.workflowid)
                        LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid --AND ssr.indate > SYSDATE
                        where nvl(c.rbtogetherflag,'0')!='0' and 
                        c.status!=0  and c.containerid='{1}'  
                     </string>.Value
        strSql = String.Format(strSql, workflowID, containerID)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
#End Region

#Region "热表提前开卡"
    ''' <summary>
    ''' 获取可提前开卡的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRBContainerAdvanceStartList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSelect As String =
           <string>
            SELECT cci.PRODUCTNAME,cci.PRODUCTID,cci.WORKFLOWSTEPNAME,cci.WORKFLOWSTEPID,cci.SPECNO, cci.WORKFLOWID,cci.WORKFLOWSTEPSEQUENCE AS SEQUENCE,cci.TOGETHERFLAG,
 cci.CONTAINERTYPE,DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,cci.MFGORDERNAME,cci.PLANQTY,sum(nvl(cci.QTY,0)) AS WIPQTY,
 cci.PLANNEDSTARTDATE,cci.PLANNEDCOMPLETIONDATE,cci.WORKCENTERNAME,cci.WORKCENTERID,cci.FACTORYID,cci.PRODUCTDESCRIPTION,cci.PRODUCTREVISION,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch, 
(cci.productname||'/'||cci.containerno) ProductBatchHidden,cci.CONTAINERNO,wb.workflowname || ':' || w.workflowrevision workflownameandrev
 FROM containercurrentinfo cci 
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN workflow w ON w.workflowid = decode(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid)
LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
LEFT JOIN Workflowstep ws ON ws.workflowid = w.workflowid
LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID
LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)
LEFT JOIN userattribute u ON u.parentid = s.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN containerstepplan csp ON csp.containerid = cci.containerid AND csp.workflowstepid = ws.workflowstepid
WHERE cci.isinstore IN (-1,0) AND cci.containerstatus = 1 AND u.attributevalue = '602'
AND cci.originalfactoryid {1} '{0}' AND ws.sequence >= decode(cci.isinstore,-1,0,0,cci.workflowstepsequence)
AND ws.wipmsglabel = '子工艺'
            </string>.Value
        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<>")

        If para.Keys.Contains("ScanContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                strSelect += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
                strSelect += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
            End If
        End If
        If para.Keys.Contains("ScanOrderName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                strSelect += " AND cci.mfgordername = '" + para("ScanOrderName") + "'"
            End If
        End If
        If para.Keys.Contains("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSelect += " AND cci.productname like '%" + para("ProductName") + "%'"
            End If
        End If
        If para.Keys.Contains("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSelect += " AND cci.containername like '%" + para("ContainerName") + "%'"
            End If
        End If
        If para.Keys.Contains("StepName") Then
            If Not String.IsNullOrWhiteSpace(para("StepName")) Then
                strSelect += " AND cci.workflowstepname like '%" + para("StepName") + "%'"
            End If
        End If

        If para.Keys.Contains("StepPlannedStartDate") Then
            If Not String.IsNullOrWhiteSpace(para("StepPlannedStartDate")) Then
                strSelect += String.Format(" AND csp.plannedstartdate >= to_date('{0}','YYYY-MM-DD')", para("StepPlannedStartDate"))
            End If
        End If

        If para.Keys.Contains("StepPlannedEndDate") Then
            If Not String.IsNullOrWhiteSpace(para("StepPlannedEndDate")) Then
                strSelect += String.Format(" AND csp.plannedstartdate <= to_date('{0}','YYYY-MM-DD')", para("StepPlannedEndDate"))
            End If
        End If

        strSelect += <string>
GROUP BY cci.PRODUCTNAME,cci.PRODUCTID,cci.WORKFLOWSTEPNAME,cci.WORKFLOWSTEPID,cci.SPECNO, cci.WORKFLOWID,cci.WORKFLOWSTEPSEQUENCE,cci.TOGETHERFLAG,
 cci.CONTAINERTYPE,cci.MFGORDERNAME,cci.PLANQTY,cci.PLANNEDSTARTDATE,cci.PLANNEDCOMPLETIONDATE,cci.WORKCENTERNAME,
 cci.WORKCENTERID,cci.FACTORYID,cci.PRODUCTDESCRIPTION,cci.PRODUCTREVISION,cci.workflowroute,cci.containerno,wb.workflowname,w.workflowrevision
                   </string>.Value

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取详细的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRBContainerAdvanceStartDetail(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSelect As String =
            <string>
         SELECT Distinct cci.*
 FROM containercurrentinfo cci 
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN workflow w ON w.workflowid = decode(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid)
LEFT JOIN Workflowstep ws ON ws.workflowid = w.workflowid
LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID
LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)
LEFT JOIN userattribute u ON u.parentid = s.factoryid AND u.userattributename = '分厂代码'
WHERE cci.isinstore IN (-1,0) AND cci.containerstatus = 1 AND u.attributevalue = '602'
AND cci.originalfactoryid {1} '{0}' AND ws.sequence >= decode(cci.isinstore,-1,0,0,cci.workflowstepsequence)
AND ws.wipmsglabel = '子工艺'
            </string>.Value

        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<>")
        strSelect += " AND cci.mfgordername = '" + para("MfgOrderName") + "'"
        strSelect += " AND cci.productid = '" + para("ProductId") + "'"
        strSelect += " AND cci.workflowid = '" + para("WorkflowId") + "'"
        strSelect += " AND cci.workflowstepid = '" + para("WorkflowstepId") + "'"
        strSelect += " AND cci.togetherflag = '" + para("TogetherFlag") + "'"
        strSelect += " AND cci.containerno = '" + para("ContainerNo") + "'"
        Return DatabaseHelper.Query_ReturnDatTable(strSelect)
    End Function

    ''' <summary>
    ''' 获取详细批次的工序
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRBContainerAdvanceStartStepDetail(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSelect As String =
            <string>
SELECT Distinct cci.containerid,cci.containername,cci.containertype,cci.productid,cci.productname,cci.productrevision productrev,
cci.workflowid,cci.mfgordername,cci.mfgorderid,cci.productdescription,cci.qty,cci.workflowstepid,cci.workflowstepname,
cci.plannedstartdate,cci.plannedcompletiondate,cci.togetherflag AS togatherflag,cci.workflowstepsequence,
ws.workflowstepname as shipstepname,ws.workflowstepid as shipstepid,s.specid,s.specno,cci.isselfmfg,
DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
csp.plannedstartdate stepplannedstartdate,decode(k.subcontainername,NULL,'未开卡','已开卡') isSubContainer
 FROM containercurrentinfo cci 
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN workflow w ON w.workflowid = decode(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid)
LEFT JOIN Workflowstep ws ON ws.workflowid = w.workflowid
LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID
LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)
LEFT JOIN userattribute u ON u.parentid = s.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN containerstepplan csp ON csp.containerid = cci.containerid AND csp.workflowstepid = ws.workflowstepid
LEFT JOIN (SELECT to_char(wm_concat(cc.containername)) subcontainername,csc.containerid,csc.workflowstepid 
FROM containersubcontainer csc 
LEFT JOIN container cc ON cc.containerid = csc.subcontainerid AND cc.status = 1
GROUP BY csc.containerid,csc.workflowstepid
)k ON k.containerid = cci.containerid AND k.workflowstepid = ws.workflowstepid
WHERE cci.isinstore IN (-1,0) AND cci.containerstatus = 1 AND u.attributevalue = '602'
AND cci.originalfactoryid {1} '{0}' AND ws.sequence >= decode(cci.isinstore,-1,0,0,cci.workflowstepsequence)
AND ws.wipmsglabel = '子工艺'
            </string>.Value

        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<>")
        strSelect += " AND cci.containerid in (" + para("ContainerIDs") + ")"

        Return DatabaseHelper.Query_ReturnDatTable(strSelect)
    End Function

#End Region

#Region "报工验证特殊过程工序"
    ''' <summary>
    ''' 验证工步报工时，工步的工序是否是有效的特殊过程工序
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="dispatchID"></param>
    ''' <param name="employeeName"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsErrorSpecialCourseSpecReport(ByVal containerID As String, ByVal dispatchID As String, ByVal employeeName As String, ByRef msg As String) As Boolean
        Dim strSql = String.Format("SELECT 1 FROM containersubcontainer csc WHERE csc.subcontainerid = '{0}'", containerID)
        If DatabaseHelper.GetSingle(strSql) Is Nothing Then '如果不是子卡，是无订单批次
            strSql = String.Format(<string>
SELECT ssr.indate,r.resourcename,ssr.resourcenames,ssr.employeenames FROM containerstepdispatch2 csd 
LEFT JOIN resourcedef r ON r.resourceid = csd.resourceid
LEFT JOIN containercurrentinfo c ON c.containerid = csd.containerid
LEFT JOIN Userattribute u1 ON u1.parentid = c.containerid AND u1.userattributename = '无订单开卡父工序ID'
LEFT JOIN Userattribute u2 ON u2.parentid = c.containerid AND u2.userattributename = '无订单开卡父工艺ID'
LEFT JOIN workflowstep ws ON ws.workflowstepid = nvl(u1.attributevalue,csd.workflowstepid)
LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
LEFT JOIN spec s ON s.specid=nvl(sb.revofrcdid,ws.specid)
LEFT JOIN SpecialCourseSpecMapp scs ON scs.workflowid = NVL(u2.attributevalue,c.workflowid)
LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid
WHERE csd.containerstepdispatchid='{0}' AND s.isspotcheck = 1
ORDER BY ssr.indate DESC
                     </string>.Value, dispatchID)
        Else '如果是转工子卡
            strSql = String.Format(<string>
SELECT ssr.indate,r.resourcename,ssr.resourcenames,ssr.employeenames FROM containerstepdispatch2 csd 
LEFT JOIN resourcedef r ON r.resourceid = csd.resourceid
LEFT JOIN containercurrentinfo c ON c.containerid = csd.containerid
LEFT JOIN workflow w ON w.workflowid = c.workflowid
LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
LEFT JOIN spec s1 ON s1.commonsubworkflow = replace(wb.workflowname,'WF-','')

LEFT JOIN containersubcontainer cc ON cc.subcontainerid = c.containerid
LEFT JOIN stepsubstep sbs ON sbs.workflowstepid = cc.workflowstepid 
LEFT JOIN workflow ww ON ww.workflowid = sbs.sonworkflowid

LEFT JOIN workflowstep wws on wws.workflowstepid = NVL2(s1.specid,sbs.sonworkflowstepid,c.workflowstepid)
LEFT JOIN specbase sb on sb.specbaseid=wws.specbaseid
LEFT JOIN spec s on s.specid=nvl(sb.revofrcdid,wws.specid)

LEFT JOIN SpecialCourseSpecMapp scs ON scs.workflowstepid = wws.workflowstepid
LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid
WHERE csd.containerstepdispatchid='{0}' AND s.isspotcheck = 1
ORDER BY ssr.indate DESC
                     </string>.Value, dispatchID)
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        '如果为空，则不存在特殊过程
        If dt.Rows.Count = 0 Then
            Return False
        End If
        '判断有效期是否为空
        If String.IsNullOrWhiteSpace(dt.Rows(0)("indate").ToString) Then
            msg = "该工序存在没有有效期的特殊过程报告"
            Return True
        End If
        Dim inData = DateTime.Parse(dt.Rows(0)("indate").ToString)
        '判断是否过期
        If inData < DateTime.Now Then
            msg = "该工序存在已过期的特殊过程报告"
            Return True
        End If
        ''判断是否包含报工人
        'If Not dt.Rows(0)("employeenames").ToString.Contains(employeeName) Then
        '    msg = String.Format("该工序的特殊过程报告未包含（编号为：{0}）的报工人员", employeeName)
        '    Return True
        'End If
        ''判断是否包含派工设备
        'If Not dt.Rows(0)("resourcenames").ToString.Contains(dt.Rows(0)("resourcename").ToString) Then
        '    msg = String.Format("该工序的特殊过程报告未包含（编号为：{0}）的设备", dt.Rows(0)("resourcename").ToString)
        '    Return True
        'End If
        Return False
    End Function
#End Region

#Region "热表无订单开卡确认维护"
    Public Function GetOrderContainerMaintList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql = <string>
                         SELECT c.productdescription productdesc,c.containername,c.qty wipqty,c.workflowstepname,c.containerid,rb.receivedate,rb.completedate,
                         e.fullname receiveName,ee.fullname completename,c.mfgordername,u1.attributevalue entrustunit,u2.attributevalue entrustcontainer
                         FROM containercurrentinfo c
                         LEFT JOIN container cc ON cc.containerid = c.containerid
                         INNER JOIN userattribute u1 ON u1.parentid = c.containerid AND u1.userattributename = '委托单位'
                         INNER JOIN userattribute u2 ON u2.parentid = c.containerid AND u2.userattributename = '委托批次'
                         LEFT JOIN rbordercontainerreceive rb ON rb.containerid = c.containerid
                         LEFT JOIN employee e ON e.employeeid = rb.receiveemployeeid
                         LEFT JOIN employee ee ON ee.employeeid = rb.completeemployeeid
                         WHERE c.containerstatus = 1 AND c.originalfactoryid = '{0}' AND rb.completedate IS NULL
         </string>.Value
        strSql = String.Format(strSql, para("FactoryID"))
        If para.ContainsKey("OrderName") Then
            If Not String.IsNullOrWhiteSpace(para("OrderName")) Then
                strSql += String.Format(" AND c.mfgordername = '{0}'", para("OrderName"))
            End If
        End If

        If para.ContainsKey("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSql += String.Format(" and c.productname like '{0}%' ", para("ProductName"))
            End If
        End If

        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSql += String.Format(" and c.ContainerName like '%{0}%' ", para("ContainerName"))
            End If
        End If

        If para.ContainsKey("EntrustUnit") Then
            If Not String.IsNullOrWhiteSpace(para("EntrustUnit")) Then
                strSql += String.Format(" and u1.attributevalue like '%{0}%' ", para("EntrustUnit"))
            End If
        End If

        If para.ContainsKey("EntrustContainer") Then
            If Not String.IsNullOrWhiteSpace(para("EntrustContainer")) Then
                strSql += String.Format(" and u2.attributevalue like '%{0}%' ", para("EntrustContainer"))
            End If
        End If

        strSql += " ORDER BY cc.originalstartdate DESC"

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageNumber").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

#End Region

#Region "热表自动开卡（转工工序） add by zhangrj 20230814"
    ''' <summary>
    ''' 获取自动开卡数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsPlanShipOutDatasByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        result.Result = True
        Try
            Dim strSql As New StringBuilder
            strSql.AppendFormat(<string>
                                    SELECT Row_Number() OVER(ORDER BY cci.mfgorderid,cci.containerid,wfs.sequence) numer,
                                    apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid,
                                    cci.productdescription description,cci.productname,cci.qty,wb.workflowname,wb.workflowname||':'||w.workflowrevision workflowinfo,
                                    w.workflowid,cci.containertype,apd.qty apsqty,apd.teamid,apd.resourceid,cci.togetherflag,
                                    DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,
                                    apd.workflowstepid,apd.workflowstepname,s.specid,s.specno,c.mfgbatch,c.isselfmfg,
                                    DECODE(apd.shiprecplanstartdate,NULL,to_char(apd.starttime,'yyyy-mm-dd'),to_char(apd.shiprecplanstartdate,'yyyy-mm-dd')) Planstarttime,
                                    DECODE(apd.shiprecplanenddate,NULL,to_char(apd.endtime,'yyyy-mm-dd'),to_char(apd.shiprecplanenddate,'yyyy-mm-dd')) Planendtime,
                                    to_char(apd.ResStartTime,'yyyy-mm-dd hh24:mi:ss') ResStartTime,to_char(apd.ResEndTime,'yyyy-mm-dd hh24:mi:ss') ResEndTime,
                                    api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排','') apstypename,api.apsplaninfoid,apd.workcenterid,
                                    '' subworkflowinfo,'' newsubworkflowinfo,'' subworkflowtype
                                    FROM containercurrentinfo cci 
                                    LEFT JOIN container c ON c.containerid = cci.containerid
                                    LEFT JOIN apsplaninfo api ON api.containerid = cci.containerid
                                    LEFT JOIN apsplandetail apd ON apd.apsplaninfoid=api.apsplaninfoid
                                    left join workflowstep wfs on wfs.workflowstepid=apd.workflowstepid
                                    LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                                    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                                    LEFT JOIN Workflow w ON w.workflowid = api.workflowid
                                    LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
                                    WHERE cci.originalfactoryid != '{0}' AND cci.isinpool=1 AND cci.containerstatus=1 AND cci.isinstore!=1  
                                    AND api.apstype2=2 AND api.isuse = 0 AND apd.isuse = 0 AND apd.status = '3' AND NVL(NVL(wfs.controlcode,s.controlcode),'0') != 'LY05'
                                    AND NOT EXISTS (SELECT 1 FROM containersubcontainer csc 
                                    LEFT JOIN container cc ON cc.containerid = csc.subcontainerid
                                    WHERE csc.containerid = apd.containerid and csc.workflowstepname = apd.workflowstepname AND cc.status =1)  
                                    AND apd.SpecFactoryID = '{0}'</string>.Value, para("FactoryID"))

            If para.ContainsKey("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                    strSql.AppendFormat(" AND cci.mfgordername = '{0}'", para("ScanOrderName"))
                End If
            End If
            If para.ContainsKey("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql.AppendFormat(" AND ((cci.containertype <> '7' and cci.containername = '{0}')", para("ScanContainerName"))
                    strSql.AppendFormat(" or (cci.containertype = '7' and cci.togetherflag = '{0}'))", para("ScanContainerName"))
                End If
            End If
            If para.ContainsKey("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql.AppendFormat(" AND cci.containername LIKE '%{0}%'", para("ContainerName"))
                End If
            End If
            If para.ContainsKey("ProductName") Then
                If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                    strSql.AppendFormat(" AND cci.productname LIKE '%{0}%'", para("ProductName"))
                End If
            End If
            If para.ContainsKey("SpecNo") Then
                If Not String.IsNullOrWhiteSpace(para("SpecNo")) Then
                    strSql.AppendFormat(" AND apd.workflowstepname like '%{0}%'", para("SpecNo"))
                End If
            End If

            If Not String.IsNullOrWhiteSpace(para("PlanStartDate")) Then
                strSql.AppendFormat(" AND apd.starttime >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("PlanStartDate"))
            End If

            If Not String.IsNullOrWhiteSpace(para("PlanEndDate")) Then
                strSql.AppendFormat(" AND apd.endtime <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("PlanEndDate"))
            End If

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
            paraPage.PageSize = Convert.ToInt32(para("PageSize"))
            paraPage.strSQL = strSql.ToString()
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 获取在制的转工子卡数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSubContainerMakeingInfoByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        result.Result = True
        Try
            Dim strSql As New StringBuilder
            strSql.AppendFormat(<string>
                                    SELECT Row_Number() OVER(ORDER BY cc.originalstartdate DESC) numer,
                                    cci.containername,cci.containerid,cci.workflowname || ':' || cci.workflowrevision workflownameandrev, cci.workflowstepname,
                                    cci.productname,cci.qty,cci.containertype,DECODE(cci.containertype,'2','批量加工','3','批量带子序号','7','单件单卡') containertypename,
                                    listagg(c.containername,',') WITHIN GROUP (ORDER BY c.containername) facontainername,cc.originalstartdate createdate
                                    FROM containercurrentinfo cci
                                    LEFT JOIN container cc ON cc.containerid = cci.containerid
                                    LEFT JOIN containersubcontainer csc ON csc.subcontainerid = cci.containerid
                                    LEFT JOIN container c ON c.containerid = csc.containerid --父卡
                                    WHERE cci.containerstatus = 1 AND cci.isinstore = 0 AND cci.originalfactoryid = '{0}' 
                                    AND csc.containersubcontainerid IS NOT NULL
                                </string>.Value, para("FactoryID"))

            If para.ContainsKey("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                    strSql.AppendFormat(" AND cci.mfgordername = '{0}'", para("ScanOrderName"))
                End If
            End If
            If para.ContainsKey("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql.AppendFormat(" AND cci.containername = '{0}'", para("ScanContainerName"))
                End If
            End If
            If para.ContainsKey("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql.AppendFormat(" AND cci.containername LIKE '%{0}%'", para("ContainerName"))
                End If
            End If
            If para.ContainsKey("ProductName") Then
                If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                    strSql.AppendFormat(" AND cci.productname LIKE '%{0}%'", para("ProductName"))
                End If
            End If
            If para.ContainsKey("SpecNo") Then
                If Not String.IsNullOrWhiteSpace(para("SpecNo")) Then
                    strSql.AppendFormat(" AND cci.workflowstepname like '%{0}%'", para("SpecNo"))
                End If
            End If
            strSql.AppendLine(<string> GROUP BY cci.mfgorderid,cci.containername,cci.containerid,cci.workflowname,cci.workflowrevision, 
                                    cci.productname,cci.qty,cci.containertype,cci.workflowstepname,cc.originalstartdate</string>.Value)
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
            paraPage.PageSize = Convert.ToInt32(para("PageSize"))
            paraPage.strSQL = strSql.ToString()
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

#Region "自动开卡逻辑"
    ''' <summary>
    ''' 自动开卡（转工工序开卡）
    ''' </summary>
    ''' <param name="param"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AutoStartContainerForShip(ByVal apsContainer As RBShipAutoStartContainerParam, ByVal m_UserInfo As CurrentUserInfo) As uMESProcessResult
        SyncLock lockObj
            Dim result As uMESProcessResult = New uMESProcessResult()
            Try
                If IsExistsSubContainer(apsContainer.ContainerID, apsContainer.WorkflowStepName) Then '验证是否存在子卡
                    result.Result = False
                    result.Message = "转工工序已存在子卡"
                    Return result
                    Exit Function
                End If

                Dim containerBaseInfo As ContainerStartInfo = New ContainerStartInfo()
                FillContainerDefaultInfo(containerBaseInfo, m_UserInfo.FactoryID)
                containerBaseInfo.ChildList = Nothing
                containerBaseInfo.MfgBatch = apsContainer.MfgBatch
                containerBaseInfo.MfgOrder = apsContainer.MfgOrderName
                containerBaseInfo.Qty = apsContainer.Qty
                containerBaseInfo.IsSelfMfg = apsContainer.IsSelfMfg
                containerBaseInfo.Factory = m_UserInfo.Factory
                containerBaseInfo.CheckTechnique = "" '暂定为空
                containerBaseInfo.UOM = "件" '暂定为件
                containerBaseInfo.Team = "" '暂定为空
                containerBaseInfo.StartEmployee = m_UserInfo.EmployeeName
                containerBaseInfo.Comment = ""
                containerBaseInfo.PlannedStartDate = apsContainer.PlanStartTime.ToString("yyyy-MM-ddTHH:mm:ss")
                containerBaseInfo.PlannedCompletionDate = apsContainer.PlanEndTime.ToString("yyyy-MM-ddTHH:mm:ss")
                containerBaseInfo.ContainerType = "2" '默认批量加工
                '-----------设置批次的工艺信息-----------------
                Dim res As Tuple(Of Boolean, String) = SetContainerWorkflowInfo(containerBaseInfo, apsContainer, m_UserInfo)
                If Not res.Item1 Then
                    InsertAutoStartErrorInfo(GetStartErrorInfo(apsContainer, res.Item2, m_UserInfo.EmployeeID))
                    result.Result = res.Item1
                    result.Message = res.Item2
                    Return result
                    Exit Function
                End If
                '-----------设置其他字段信息-----------------
                res = SetContainerBaseInfo(containerBaseInfo, apsContainer, m_UserInfo)
                If Not res.Item1 Then
                    InsertAutoStartErrorInfo(GetStartErrorInfo(apsContainer, res.Item2, m_UserInfo.EmployeeID))
                    result.Result = res.Item1
                    result.Message = res.Item2
                    Return result
                    Exit Function
                End If
                '----------如果主制批次是带子序号或者单件单卡，都转换成批量带子序号--------------
                If apsContainer.ContainerType = "3" OrElse apsContainer.ContainerType = "7" Then
                    res = SetChildContainerInfo(containerBaseInfo, apsContainer)

                    If Not res.Item1 Then
                        InsertAutoStartErrorInfo(GetStartErrorInfo(apsContainer, res.Item2, m_UserInfo.EmployeeID))
                        result.Result = res.Item1
                        result.Message = res.Item2
                        Return result
                        Exit Function
                    End If
                End If
                '-----------增加批次属性-----------------
                AddContainerAttribute(containerBaseInfo, m_UserInfo)
                '-----------api创建批次-----------------
                Dim serverInfo As ServerHostInfo = New ServerHostInfo()
                serverInfo.UserName = m_UserInfo.APIUserName
                serverInfo.Password = m_UserInfo.APIPassword
                containerBaseInfo.ServerHostInfo = serverInfo
                Dim tempResult As Camstar.WebClient.WebServicesProxy.InSiteWebServices.ResultStatus = m_ContainerObj.StartContainer(containerBaseInfo)
                If tempResult.IsError Then
                    InsertAutoStartErrorInfo(GetStartErrorInfo(apsContainer, "开卡报错：" & tempResult.Message, m_UserInfo.EmployeeID))
                    result.Result = False
                    result.Message = tempResult.Message
                    Return result
                    Exit Function
                End If
                '-----------保存主卡子卡的对应关系-----------------
                res = SaveContainerSubContainer(containerBaseInfo, apsContainer, m_UserInfo)
                If Not res.Item1 Then '开卡成功，但主子卡对应关系保持失败，提示出来，并插入日志
                    InsertAutoStartErrorInfo(GetStartErrorInfo(apsContainer, "开卡成功，" & res.Item2, m_UserInfo.EmployeeID))
                    result.Result = res.Item1
                    result.Message = "开卡成功，" & res.Item2
                    Return result
                    Exit Function
                End If
                '-----------保存批次新属性（连续转工序的上下序批次的关系）-----------------
                res = SaveMainContainerNewAttribute(apsContainer.ContainerID, containerBaseInfo.ContainerName, apsContainer.WorkflowStepID)
                If Not res.Item1 Then '开卡成功，但批次新属性添加失败，提示出来，并插入日志
                    InsertAutoStartErrorInfo(GetStartErrorInfo(apsContainer, "开卡成功，" & res.Item2, m_UserInfo.EmployeeID))
                    result.Result = res.Item1
                    result.Message = "开卡成功，" & res.Item2
                    Return result
                    Exit Function
                End If
                DeleAutoStartErrorInfo(apsContainer.ContainerID, apsContainer.WorkflowStepID) '开卡成功，删除出错日志
                'SaveContainerApsPoolInfo(containerBaseInfo.ContainerName, containerBaseInfo.WorkCenter) '开卡成功，更新批次排产池
                result.Result = True
                result.Message = "开卡成功"
            Catch ex As Exception
                InsertAutoStartErrorInfo(GetStartErrorInfo(apsContainer, "开卡失败，原因：" & ex.Message, m_UserInfo.EmployeeID))
                result.Result = False
                result.Message = "开卡失败，原因：" & ex.Message
            End Try
            Return result
        End SyncLock
    End Function

    ''' <summary>
    ''' 设置批次的工艺信息
    ''' </summary>
    ''' <param name="containerBaseInfo"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SetContainerWorkflowInfo(ByRef containerBaseInfo As ContainerStartInfo, ByVal apsContainer As RBShipAutoStartContainerParam, ByVal m_UserInfo As CurrentUserInfo) As Tuple(Of Boolean, String)
        Dim message As String = String.Empty
        Dim dic As Dictionary(Of String, String) = New Dictionary(Of String, String)()
        Dim strsql As String = String.Format("SELECT 1 FROM workflowstep w WHERE w.workflowstepid = '{0}' AND w.wipmsglabel = '子工艺'", apsContainer.WorkflowStepID)

        If DatabaseHelper.Query_ReturnDatTable(strsql).Rows.Count = 0 Then
            message = String.Format("转工工序：{0}没有定义【子工艺】标识", apsContainer.WorkflowStepName)
            Return New Tuple(Of Boolean, String)(False, message)
        End If
        '查询主制分厂工序的子工艺信息（热表工艺）
        Dim subWorkflowDt As DataTable = m_CommonObj.GetSubWorkflowInfo(New Dictionary(Of String, String) From {
            {"WorkflowStepID", apsContainer.WorkflowStepID},
            {"FactoryID", m_UserInfo.FactoryID}
        })
        If subWorkflowDt.Rows.Count = 0 Then
            message = String.Format("转工工序：{0}未找到子工艺信息", apsContainer.WorkflowStepName)
            Return New Tuple(Of Boolean, String)(False, message)
        End If
        '设置开始工序
        Dim workflowStepDt = m_CommonObj.GetWorkFlowSetpsByWorkflowID(subWorkflowDt.Rows(0)("subworkflowid").ToString())

        If workflowStepDt.Rows.Count = 0 Then
            message = String.Format("子工艺：{0}-{1}未找到工序信息", subWorkflowDt.Rows(0)("subworkflowname").ToString(), subWorkflowDt.Rows(0)("subworkflowrevision").ToString())
            Return New Tuple(Of Boolean, String)(False, message)
        End If
        containerBaseInfo.BeginStep = workflowStepDt.Rows(0)("WORKFLOWSTEPNAME").ToString()
        ''如果开卡的工序和转工工序不一致，则提示，不开卡
        'If containerBaseInfo.BeginStep.ToString() <> apsContainer.WorkflowStepName Then
        '    message = String.Format("转工工序：{0}下的子工艺：{1}的工序（{2}）和转工工序不一致", apsContainer.WorkflowStepName, subWorkflowDt.Rows(0)("subworkflowname").ToString(), containerBaseInfo.BeginStep.ToString())
        '    Return New Tuple(Of Boolean, String)(False, message)
        'End If

        '设置工艺信息
        containerBaseInfo.MfgWorkflowName = subWorkflowDt.Rows(0)("subworkflowname").ToString()
        containerBaseInfo.MfgWorkflowRevision = subWorkflowDt.Rows(0)("subworkflowrevision").ToString()
        containerBaseInfo.WorkflowName = subWorkflowDt.Rows(0)("subworkflowname").ToString()
        containerBaseInfo.WorkflowRevision = subWorkflowDt.Rows(0)("subworkflowrevision").ToString()
        containerBaseInfo.MfgWorkflowuseROR = False
        containerBaseInfo.ProductuseROR = False
        Dim subProductDt As DataTable = m_CommonObj.GetProductInfoByID(subWorkflowDt.Rows(0)("subproductid").ToString())

        If subProductDt.Rows.Count > 0 Then
            containerBaseInfo.ProductName = subProductDt.Rows(0)("PRODUCTNAME").ToString()
            containerBaseInfo.ProductRevision = subProductDt.Rows(0)("productrevision").ToString()
        End If
        '查询子工艺关联的子件号,可能一对多，系统处理，和主件号匹配
        subProductDt = DatabaseHelper.Query_ReturnDatTable(String.Format(<string>select pw.workflowid,pb.productname,p.productrevision from productworkflowmapping pw
                                                                left join product p on p.productid=pw.productid
                                                                left join productbase pb on pb.productbaseid=p.productbaseid
                                                                where pw.workflowid='{0}'</string>.Value, subWorkflowDt.Rows(0)("subworkflowid").ToString()))

        If subProductDt.Rows.Count > 0 Then
            Dim subProduct As DataRow() = subProductDt.[Select](String.Format("productname like '{0}%'", apsContainer.ProductName))

            If subProduct.Length > 0 Then
                containerBaseInfo.ProductName = subProduct(0)("PRODUCTNAME").ToString()
                containerBaseInfo.ProductRevision = subProduct(0)("productrevision").ToString()
            End If
        End If
        '查询子工艺最新的工艺
        Dim newestSubWorkflow As DataTable = m_CommonObj.GetWorkflowInfoByNameAndRev(New Dictionary(Of String, String)() From {
            {"WorkflowName", subWorkflowDt.Rows(0)("subworkflowname").ToString()}
        })
        Dim drNewestSubWorkflow = newestSubWorkflow.AsEnumerable().OrderByDescending(Function(row) row("workflowid").ToString()).FirstOrDefault()
        Dim newestworkflowrev As String = drNewestSubWorkflow("workflowrevision").ToString()

        If Not String.IsNullOrWhiteSpace(newestworkflowrev) AndAlso newestworkflowrev <> containerBaseInfo.WorkflowRevision.ToString() Then
            containerBaseInfo.MfgWorkflowRevision = newestworkflowrev
            containerBaseInfo.WorkflowRevision = newestworkflowrev
        End If

        ''判断是否有工艺附件，没有则提示，不可开卡
        If Not IsExistsWorkflowDocInfo(containerBaseInfo.WorkflowName, containerBaseInfo.WorkflowRevision) Then
            message = String.Format("子工艺：{0}-{1}工艺附件信息不全，无法开卡", containerBaseInfo.WorkflowName, containerBaseInfo.WorkflowRevision)
            Return New Tuple(Of Boolean, String)(False, message)
        End If

        Return New Tuple(Of Boolean, String)(True, message)
    End Function

    ''' <summary>
    ''' 设置批次基础信息
    ''' </summary>
    ''' <param name="containerBaseInfo"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SetContainerBaseInfo(ByRef containerBaseInfo As ContainerStartInfo, ByVal apsContainer As RBShipAutoStartContainerParam, ByVal m_UserInfo As CurrentUserInfo) As Tuple(Of Boolean, String)
        Dim sql As String = String.Format(<string>SELECT coi.customobjectitemsdesc,coi.customobjectitemsname FROM customobject co 
                        LEFT JOIN customobjectitems coi ON coi.customobjectid = co.customobjectid AND coi.isuse = 0
                        LEFT JOIN workflow w ON w.workflowtype = coi.customobjectitemsname
                        LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
                        WHERE co.customobjectname = '热表工艺类型工区关系' AND co.isuse = 0 
                        AND wb.workflowname = '{0}' AND w.workflowrevision = '{1}'</string>.Value, containerBaseInfo.WorkflowName, containerBaseInfo.WorkflowRevision)
        Dim dtDic As DataTable = DatabaseHelper.Query_ReturnDatTable(sql)

        If dtDic.Rows.Count = 0 Then
            Return New Tuple(Of Boolean, String)(False, "配置项【热表工艺类型工区关系】未找到相应的工艺类型")
        End If

        Dim objItem As String() = dtDic.Rows(0)("customobjectitemsdesc").ToString().Split(";")

        If objItem.Length < 2 Then
            Return New Tuple(Of Boolean, String)(False, "配置项【热表工艺类型工区关系】内容无效")
        End If

        Dim rbType As RbType

        Select Case objItem(0)
            Case "R"
                rbType = rbType.R
            Case "B"
                rbType = rbType.B
            Case "T"
                rbType = rbType.T
            Case Else
                Return New Tuple(Of Boolean, String)(False, "配置项【热表工艺类型工区关系】内容无效")
        End Select

        Dim dtWorkCenter As DataTable = m_CommonObj.GetWorkCenterByFacoryID(m_UserInfo.FactoryID, False)

        If dtWorkCenter.AsEnumerable().Where(Function(p) p("WORKCENTERNAME").ToString() = objItem(1)).Count() = 0 Then
            Return New Tuple(Of Boolean, String)(False, "配置项【热表工艺类型工区关系】中工区无效")
        End If

        Dim prefix As String = containerBaseInfo.ProductName & "/"
        Dim res As Tuple(Of String, String) = getContainerNos(apsContainer, rbType, prefix)
        containerBaseInfo.ContainerName = prefix & res.Item2
        containerBaseInfo.TogetherFlag = prefix & res.Item2
        containerBaseInfo.WorkCenter = objItem(1)
        containerBaseInfo.RbTogetherflag = res.Item1
        containerBaseInfo.RbContainerType = Integer.Parse((CType(rbType, [Enum])).ToString("D")).ToString()
        Dim conFamily As DataTable = GetContainerProductFamily(apsContainer.ContainerID)
        Dim listFamily As Dictionary(Of Integer, String) = New Dictionary(Of Integer, String)()

        For Each productfamily As DataRow In conFamily.Rows
            listFamily.Add(Integer.Parse(productfamily("sequence").ToString()), productfamily("productfamilyname").ToString())
        Next

        containerBaseInfo.ProductFamilyList = listFamily
        Return New Tuple(Of Boolean, String)(True, "获取成功")
    End Function

    ''' <summary>
    ''' 热表批次类型枚举
    ''' </summary>
    ''' <remarks></remarks>
    Private Enum RbType
        R = 1
        B = 2
        T = 3
    End Enum

    ''' <summary>
    ''' 获取热表的大批次和批次号
    ''' </summary>
    ''' <param name="rbType">热表类型</param>
    ''' <param name="prefix">批次前缀</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function getContainerNos(ByVal apsContainer As RBShipAutoStartContainerParam, ByVal rbType As RbType, ByVal prefix As String) As Tuple(Of String, String)
        Dim maxIndex As Integer = 0
        Dim rbTogetherflag As String = String.Format("{0}-{1}-", DateTime.Now.ToString("yyMM"), [Enum].GetName(GetType(RbType), rbType))
        Dim faTogetherFlags As String = String.Empty
        Dim faTogetherFlagDt = GetRelativesFatogetherflag(apsContainer.TogetherFlag)

        For Each r As DataRow In faTogetherFlagDt.Rows
            faTogetherFlags += "'" & r("faTogetherFlag").ToString() & "',"
        Next

        faTogetherFlags = faTogetherFlags.TrimEnd(",")
        Dim subContainerDt = GetSubContainer(New Dictionary(Of String, String)() From {
            {"FaTogetherFlags", faTogetherFlags},
            {"SubRbContainerType", Integer.Parse((CType(rbType, [Enum])).ToString("D")).ToString()}
        })

        If subContainerDt.Rows.Count > 0 Then
            rbTogetherflag = subContainerDt.Rows(0)("RbTogetherflag").ToString()
        Else
            maxIndex = GetNewestRbTogetherflag(rbTogetherflag)
            rbTogetherflag += (System.Threading.Interlocked.Increment(maxIndex)).ToString("D4")
        End If

        maxIndex = GetNewestContainerNo(prefix & rbTogetherflag & "-")
        Dim rbContainerNo As String = rbTogetherflag & "-" & (System.Threading.Interlocked.Increment(maxIndex)).ToString("D2")
        Return New Tuple(Of String, String)(rbTogetherflag, rbContainerNo)
    End Function

    ''' <summary>
    ''' 设置子序号
    ''' </summary>
    ''' <param name="containerBaseInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SetChildContainerInfo(ByRef containerBaseInfo As ContainerStartInfo, ByVal apsContainer As RBShipAutoStartContainerParam) As Tuple(Of Boolean, String)
        Dim res As Boolean = True
        Dim resMsg As String = ""
        Dim dtChildContainer As DataTable = If(apsContainer.ContainerType = "3", GetChildContainer(apsContainer.ContainerID, apsContainer.WorkflowStepID), m_CommonObj.GetTableInfo("container", "containerid", apsContainer.ContainerID))
        Dim childDt As DataTable = New DataTable()
        childDt.Columns.Add("ContainerName")
        childDt.Columns.Add("Qty")
        childDt.Columns.Add("ChildBeginStep")
        childDt.Columns.Add("TogetherFlag")
        childDt.Columns.Add("QualificationNum")
        childDt.Columns.Add("RbContainerType")

        For Each item As DataRow In dtChildContainer.Rows

            If String.IsNullOrWhiteSpace(item("qualificationnum").ToString()) Then
                res = False
                resMsg = "主卡子序号不存在"
                Exit For
            End If

            Dim childRow = childDt.NewRow()
            childRow("ContainerName") = containerBaseInfo.ContainerName + item("qualificationnum").ToString()
            childRow("Qty") = 1
            childRow("ChildBeginStep") = containerBaseInfo.BeginStep
            childRow("TogetherFlag") = containerBaseInfo.TogetherFlag
            childRow("QualificationNum") = item("qualificationnum").ToString()
            childRow("RbContainerType") = containerBaseInfo.RbContainerType
            childDt.Rows.Add(childRow)
        Next

        containerBaseInfo.ChildList = childDt
        containerBaseInfo.ContainerType = "3"
        Return New Tuple(Of Boolean, String)(res, resMsg)
    End Function

    ''' <summary>
    ''' 添加批次属性
    ''' </summary>
    ''' <param name="containerBase"></param>
    ''' <remarks></remarks>
    Private Sub AddContainerAttribute(ByRef containerBase As ContainerStartInfo, ByVal m_UserInfo As CurrentUserInfo)
        Dim userattributedt As DataTable = New DataTable()
        userattributedt.Columns.Add("Name")
        userattributedt.Columns.Add("Value")
        Dim dr As DataRow = userattributedt.NewRow()
        dr("Name") = "CheckTechnique"
        dr("Value") = containerBase.CheckTechnique
        userattributedt.Rows.Add(dr)
        dr = userattributedt.NewRow()
        dr("Name") = "BeginStep"
        dr("Value") = containerBase.BeginStep
        userattributedt.Rows.Add(dr)

        ''工艺确认流程
        Dim strSql = String.Format(<string>
                        SELECT c.containerid,wf.workflowtype2 FROM workflow wf
                        LEFT JOIN Workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
                        LEFT JOIN container c ON c.mfgworkflowid = wf.workflowid AND c.status = 1
                        WHERE wfb.workflowname = '{0}' AND wf.workflowrevision = '{1}'
                     </string>.Value, containerBase.MfgWorkflowName, containerBase.MfgWorkflowRevision)
        Dim dtWorkflow = DatabaseHelper.Query_ReturnDatTable(strSql)
        '该工艺不存在任何批次，或者工艺类型是临时工艺的，都要走工艺确认流程
        If dtWorkflow.Rows.Count > 0 Then '理论上不会为空，但是还是做判断
            If String.IsNullOrWhiteSpace(dtWorkflow.Rows(0)("containerid").ToString()) Or dtWorkflow.Rows(0)("workflowtype2").ToString().Trim() = "临时工艺" Then
                Dim levelAttributes As DataTable = m_CommonObj.GetFatoryAttributes(m_UserInfo.FactoryID)
                Dim datarows = levelAttributes.Select("USERATTRIBUTENAME='工艺确认流程'")
                If datarows.Length > 0 Then
                    Dim strWorkflows() = datarows(0)("ATTRIBUTEVALUE").ToString.Split("：")
                    containerBase.WorkflowName = strWorkflows.First
                    containerBase.WorkflowRevision = strWorkflows.Last
                End If
                dr = userattributedt.NewRow()
                dr("Name") = "工艺确认"
                dr("Value") = ""
                userattributedt.Rows.Add(dr)
            End If
        End If

        If containerBase.MonthPlan IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace(containerBase.MonthPlan.ToString()) Then
            dr = userattributedt.NewRow()
            dr("Name") = "monthPlanName"
            dr("Value") = containerBase.MonthPlan
            userattributedt.Rows.Add(dr)
        End If

        'If containerBase.IsSelfMfg = "0" Then
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "材料牌号"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "实发数/顺序号"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "熔炉号母"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "检验编号"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "库房保管"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "器材检验"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "代料牌号"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "代料规格"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "代料单号"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "平行作业单号"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "平行作业工序"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        '    dr = userattributedt.NewRow()
        '    dr("Name") = "顾客代表"
        '    dr("Value") = ""
        '    userattributedt.Rows.Add(dr)
        'End If

        userattributedt.AcceptChanges()
        containerBase.AttributeList = userattributedt
    End Sub

    ''' <summary>
    ''' 保存主卡子卡对应关系
    ''' </summary>
    ''' <param name="containerBaseInfo"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SaveContainerSubContainer(ByVal containerBaseInfo As ContainerStartInfo, ByVal apsContainer As RBShipAutoStartContainerParam, ByVal m_UserInfo As CurrentUserInfo) As Tuple(Of Boolean, String)
        Try
            Dim subWorkflowDt As DataTable = m_CommonObj.GetWorkflowInfoByNameAndRev(New Dictionary(Of String, String)() From {
                {"Rev", containerBaseInfo.MfgWorkflowRevision.ToString()},
                {"WorkflowName", containerBaseInfo.MfgWorkflowName.ToString()}
            })
            Dim subWorkflowId As String = subWorkflowDt.Rows(0)("workflowid").ToString()
            Dim subProcessNo As String = subWorkflowDt.Rows(0)("process_no").ToString()
            Dim subContainerId As String = m_CommonObj.GetTableInfo("container", "containername", containerBaseInfo.ContainerName.ToString()).Rows(0)("containerid").ToString()
            Dim exuteEntitys As List(Of ExcuteEntity) = New List(Of ExcuteEntity)()

            If apsContainer.ContainerType = "3" Then
                Dim childContainer As DataTable = GetChildContainer(apsContainer.ContainerID, apsContainer.WorkflowStepID)

                For Each item As DataRow In childContainer.Rows
                    Dim exuteEntity As ExcuteEntity = New ExcuteEntity("containersubcontainer", ExcuteEntity.ExcuteTypeEnum.insert)
                    exuteEntity.ExcuteFileds = New List(Of FieldEntity)() From {
                        New FieldEntity("containerid", apsContainer.ContainerID, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("workflowstepid", apsContainer.WorkflowStepID, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("containersubcontainerid", Guid.NewGuid().ToString(), FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("createemployeeid", m_UserInfo.EmployeeID, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("faqty", apsContainer.Qty, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("WorkflowStepName", apsContainer.WorkflowStepName, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("SubWorkflowID", subWorkflowId, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("SubProcessNo", subProcessNo, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("createdate", DateTime.Now, FieldEntity.FieldTypeEnum.DateTime),
                        New FieldEntity("subcontainerid", subContainerId, FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("childcontainerid", item("CONTAINERID").ToString(), FieldEntity.FieldTypeEnum.Str),
                        New FieldEntity("isuse", 0, FieldEntity.FieldTypeEnum.Numer)
                    }
                    exuteEntitys.Add(exuteEntity)
                Next
            Else
                Dim exuteEntity As ExcuteEntity = New ExcuteEntity("containersubcontainer", ExcuteEntity.ExcuteTypeEnum.insert)
                exuteEntity.ExcuteFileds = New List(Of FieldEntity)() From {
                    New FieldEntity("containerid", apsContainer.ContainerID, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("workflowstepid", apsContainer.WorkflowStepID, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("containersubcontainerid", Guid.NewGuid().ToString(), FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("createemployeeid", m_UserInfo.EmployeeID, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("faqty", apsContainer.Qty, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("WorkflowStepName", apsContainer.WorkflowStepName, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("SubWorkflowID", subWorkflowId, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("SubProcessNo", subProcessNo, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("createdate", DateTime.Now, FieldEntity.FieldTypeEnum.DateTime),
                    New FieldEntity("subcontainerid", subContainerId, FieldEntity.FieldTypeEnum.Str),
                    New FieldEntity("isuse", 0, FieldEntity.FieldTypeEnum.Numer)
                }
                exuteEntitys.Add(exuteEntity)
            End If

            DatabaseHelper.ExecuteDataByEntitys(exuteEntitys)
            Return New Tuple(Of Boolean, String)(True, "保存成功")
        Catch ex As Exception
            Return New Tuple(Of Boolean, String)(False, "保存主卡子卡对应关系出错：" & ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' 获取错误参数
    ''' </summary>
    ''' <param name="errorMsg"></param>
    ''' <param name="employeeID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetStartErrorInfo(ByVal apsContainer As RBShipAutoStartContainerParam, ByVal errorMsg As String, ByVal employeeID As String) As Dictionary(Of String, String)
        Return New Dictionary(Of String, String) From {
            {"ContainerID", apsContainer.ContainerID},
            {"ContainerName", apsContainer.ContainerName},
            {"WorkflowID", apsContainer.WorkflowID},
            {"WorkflowNameAndRev", apsContainer.WorkflowInfo},
            {"WorkflowstepID", apsContainer.WorkflowStepID},
            {"WorkflowstepName", apsContainer.WorkflowStepName},
            {"Message", errorMsg},
            {"EmployeeID", employeeID}
        }
    End Function
#End Region

    ''' <summary>
    ''' 保存主卡批次属性（新的批次属性表）
    ''' </summary>
    ''' <param name="faContainerID">主制批次ID</param>
    ''' <param name="subContainerName">子卡批次</param>
    ''' <param name="faWorkflowStepID">主制工序</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveMainContainerNewAttribute(ByVal faContainerID As String, ByVal subContainerName As String, ByVal faWorkflowStepID As String) As Tuple(Of Boolean, String)
        Try
            Dim strSql = String.Format(<string>
                        SELECT DECODE(sp.specid, null, '', wsp.workflowstepname) preStepName,
                               DECODE(sn.specid, null, '', wsn.workflowstepname) nextStepName
                        FROM workflowstep ws 
                        LEFT JOIN workflowstep wsp ON wsp.workflowid = ws.workflowid AND wsp.sequence = (ws.sequence -1) --查询上一序
                        LEFT JOIN specbase sbp ON sbp.specbaseid = wsp.specbaseid
                        LEFT JOIN spec sp ON sp.specid = NVL(sbp.revofrcdid,wsp.specid) AND sp.factoryid = '{1}'
                        LEFT JOIN workflowstep wsn ON wsn.workflowid = ws.workflowid AND wsn.sequence = (ws.sequence + 1) --查询下一序
                        LEFT JOIN specbase sbn ON sbn.specbaseid = wsn.specbaseid
                        LEFT JOIN spec sn ON sn.specid = NVL(sbn.revofrcdid,wsn.specid) AND sn.factoryid = '{1}'
                        WHERE ws.workflowstepid = '{0}'   
                     </string>.Value, faWorkflowStepID, rbFactoryID)
            Dim dtSpec = DatabaseHelper.Query_ReturnDatTable(strSql) '查询转工序的上序和下序，用于判断是否连续转工序
            Dim deleteSql = "DELETE containerattribute ca WHERE ca.containerid = '{0}' AND ca.attributename = '下序批次名'" '删除下序批次名属性
            Dim insertSql = "INSERT INTO containerattribute(containerattributeid,containerid,attributename,attributevalue) VALUES('{0}','{1}','下序批次名','{2}')" '插入批次属性表
            '-------------------------------如果存在上一序---------------------------------
            If Not String.IsNullOrWhiteSpace(dtSpec.Rows(0)("preStepName").ToString()) Then
                strSql = String.Format(<stirng>
                                    SELECT DISTINCT c.containername,c.containerid FROM containersubcontainer csc
                                    LEFT JOIN container c ON c.containerid = csc.subcontainerid
                                    WHERE csc.containerid = '{0}' AND csc.workflowstepname = '{1}'  AND c.status = 1
                               </stirng>.Value, faContainerID, dtSpec.Rows(0)("preStepName").ToString())
                Dim dtPreContainer = DatabaseHelper.Query_ReturnDatTable(strSql)
                If dtPreContainer.Rows.Count > 0 Then
                    '删除上一序的子卡批次属性表
                    DatabaseHelper.ExecuteSql(String.Format(deleteSql, dtPreContainer.Rows(0)("containerid").ToString()))
                    '将当前批次插入到上一序的子卡批次的属性表中
                    DatabaseHelper.ExecuteSql(String.Format(insertSql, Guid.NewGuid.ToString(), dtPreContainer.Rows(0)("containerid").ToString(), subContainerName))
                End If
            End If
            '-------------------------------如果存在下一序---------------------------------
            If Not String.IsNullOrWhiteSpace(dtSpec.Rows(0)("nextStepName").ToString()) Then
                strSql = String.Format(<stirng>
                                   SELECT c.containerid FROM container c WHERE c.containername = '{0}' AND c.status = 1
                               </stirng>.Value, subContainerName)
                Dim dtCurContainer = DatabaseHelper.Query_ReturnDatTable(strSql) '查询出当前批次的批次ID
                strSql = String.Format(<stirng>
                                    SELECT DISTINCT c.containername,c.containerid FROM containersubcontainer csc
                                    LEFT JOIN container c ON c.containerid = csc.subcontainerid
                                    WHERE csc.containerid = '{0}' AND csc.workflowstepname = '{1}'  AND c.status = 1
                               </stirng>.Value, faContainerID, dtSpec.Rows(0)("nextStepName").ToString())
                Dim dtNextContainer = DatabaseHelper.Query_ReturnDatTable(strSql) '查询出下一序的子卡批次信息
                If dtNextContainer.Rows.Count > 0 Then
                    '删除当前批次的属性表
                    DatabaseHelper.ExecuteSql(String.Format(deleteSql, dtCurContainer.Rows(0)("containerid").ToString()))
                    '将下一序的子卡批次插入到当前批次的属性表中
                    DatabaseHelper.ExecuteSql(String.Format(insertSql, Guid.NewGuid.ToString(), dtCurContainer.Rows(0)("containerid").ToString(), dtNextContainer.Rows(0)("containername").ToString()))
                End If
            End If
            Return New Tuple(Of Boolean, String)(True, "保存成功")
        Catch ex As Exception
            Return New Tuple(Of Boolean, String)(False, "保存批次新属性出错：" & ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' 获取开卡出错日志
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerErrorInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        result.Result = True
        Dim strSql As New StringBuilder
        strSql.AppendLine(<string>
                         SELECT Row_Number() OVER( ORDER BY r.containerid,r.workflowid,r.workflowstepid) numer,r.*
                         FROM RbStartContainerErrorInfo r
                         WHERE 1=1
                     </string>.Value)
        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSql.AppendFormat(" AND r.containername like '%{0}%'", para("ContainerName"))
            End If
        End If
        If para.ContainsKey("SpecNo") Then
            If Not String.IsNullOrWhiteSpace(para("SpecNo")) Then
                strSql.AppendFormat(" AND r.workflowstepname like '%{0}%'", para("SpecNo"))
            End If
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize"))
        paraPage.strSQL = strSql.ToString()
        result = DatabaseHelper.RunProcedureForPage(paraPage)
        Return result
    End Function

    ''' <summary>
    ''' 插入自动开卡报错记录信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <remarks></remarks>
    Public Sub InsertAutoStartErrorInfo(ByVal para As Dictionary(Of String, String))
        Dim strSql = <string> INSERT INTO RbStartContainerErrorInfo(Rbstartcontainererrorinfoid,Containername,Containerid,workflowid,Workflownameandrev,Workflowstepid,Workflowstepname,Errormessage,Createdate,Createemployeeid)
                              VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',SYSDATE,'{8}') </string>.Value
        strSql = String.Format(strSql, Guid.NewGuid.ToString(), para("ContainerName"), para("ContainerID"), para("WorkflowID"), para("WorkflowNameAndRev"), para("WorkflowstepID"), para("WorkflowstepName"), para("Message"), para("EmployeeID"))
        DatabaseHelper.ExecuteSql(strSql)
    End Sub

    ''' <summary>
    ''' 删除自动开卡报错记录信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowstepID"></param>
    ''' <remarks></remarks>
    Public Sub DeleAutoStartErrorInfo(ByVal containerID As String, ByVal workflowstepID As String)
        Dim strSql = "DELETE RbStartContainerErrorInfo r WHERE r.containerid = '{0}' AND r.workflowstepid = '{1}'"
        strSql = String.Format(strSql, containerID, workflowstepID)
        DatabaseHelper.ExecuteSql(strSql)
    End Sub

    ''' <summary>
    ''' 转工排产自动开卡（用于转工排产计划接收时调用）
    ''' </summary>
    ''' <param name="apsPlanDetailIds"></param>
    ''' <param name="userInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveApsShipStartContainer(ByVal apsPlanDetailIds As String, ByVal userInfo As CurrentUserInfo) As uMESProcessResult
        Dim result As New uMESProcessResult
        Dim msg As String = String.Empty
        Dim res As Boolean = True
        Dim sql = String.Format(<string>
                      SELECT Row_Number() OVER(ORDER BY cci.mfgorderid,cci.containerid,wfs.sequence) numer,
                                    apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid,
                                    cci.productdescription description,cci.productname,cci.qty,wb.workflowname,wb.workflowname||':'||w.workflowrevision workflowinfo,
                                    w.workflowid,cci.containertype,apd.qty apsqty,apd.teamid,apd.resourceid,cci.togetherflag,
                                    DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,
                                    apd.workflowstepid,apd.workflowstepname,s.specid,s.specno,c.mfgbatch,c.isselfmfg,
                                    DECODE(apd.shiprecplanstartdate,NULL,to_char(apd.starttime,'yyyy-mm-dd'),to_char(apd.shiprecplanstartdate,'yyyy-mm-dd')) Planstarttime,
                                    DECODE(apd.shiprecplanenddate,NULL,to_char(apd.endtime,'yyyy-mm-dd'),to_char(apd.shiprecplanenddate,'yyyy-mm-dd')) Planendtime
                                    FROM apsplandetail apd 
                                    LEFT JOIN apsplaninfo api ON api.apsplaninfoid = apd.apsplaninfoid
                                    LEFT JOIN containercurrentinfo cci ON cci.containerid = api.containerid
                                    LEFT JOIN container c ON c.containerid = api.containerid
                                    left join workflowstep wfs on wfs.workflowstepid=apd.workflowstepid
                                    LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                                    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                                    LEFT JOIN Workflow w ON w.workflowid = api.workflowid
                                    LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
                                    WHERE apd.apsplandetailid IN ({0})
                                    AND NOT EXISTS (SELECT 1 FROM containersubcontainer csc 
                                    LEFT JOIN container cc ON cc.containerid = csc.subcontainerid
                                    WHERE csc.containerid = apd.containerid and csc.workflowstepname = apd.workflowstepname AND cc.status != 0)  
                  </string>.Value, apsPlanDetailIds)
        Dim dtAps = DatabaseHelper.Query_ReturnDatTable(sql)
        For Each dr As DataRow In dtAps.Rows
            result = AutoStartContainerForShip(GetAutoContainerTransParam(dr), userInfo) '自动开卡
            If Not result.Result Then
                res = False
                msg += String.Format("批次：{0}的转工序：{1}自动开卡失败，原因：{2}。", dr("containername").ToString(), dr("workflowstepname").ToString(), result.Message) '某一个批次转工序开卡失败，只提示，不中断
            End If
        Next
        result.Result = res
        result.Message = If(res, "开卡成功", msg)
        Return result
    End Function

    Private Function GetAutoContainerTransParam(ByVal drAps As DataRow) As RBShipAutoStartContainerParam
        Dim param As New RBShipAutoStartContainerParam
        param.ContainerID = drAps("containerid").ToString()
        param.ContainerName = drAps("ContainerName").ToString()
        param.ContainerType = drAps("ContainerType").ToString()
        param.IsSelfMfg = drAps("IsSelfMfg").ToString()
        param.MfgBatch = drAps("MfgBatch").ToString()
        param.MfgOrderName = drAps("MfgOrderName").ToString()
        param.PlanEndTime = Date.Parse(drAps("PlanEndTime").ToString())
        param.PlanStartTime = Date.Parse(drAps("PlanStartTime").ToString())
        param.ProductName = drAps("ProductName").ToString()
        param.Qty = drAps("Qty").ToString()
        param.SpecNo = drAps("SpecNo").ToString()
        param.TogetherFlag = drAps("TogetherFlag").ToString()
        param.WorkflowID = drAps("WorkflowID").ToString()
        param.WorkflowInfo = drAps("WorkflowInfo").ToString()
        param.WorkflowStepID = drAps("WorkflowStepID").ToString()
        param.WorkflowStepName = drAps("WorkflowStepName").ToString()
        Return param
    End Function

#End Region

    ''' <summary>
    ''' 是否存在子卡信息
    ''' </summary>
    ''' <param name="containerID">父卡批次ID</param>
    ''' <param name="workflowstepName">父卡工序</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsExistsSubContainer(ByVal containerID As String, ByVal workflowstepName As String) As Boolean
        Dim sql = <string>
                      SELECT 1 FROM containersubcontainer csc
                      LEFT JOIN container c ON c.containerid = csc.subcontainerid
                      WHERE csc.containerid = '{0}' AND csc.workflowstepname = '{1}' AND c.status != 0
                  </string>.Value
        sql = String.Format(sql, containerID, workflowstepName)

        If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 推送商品工时
    ''' </summary>
    ''' <param name="containerName"></param>
    ''' <param name="employeeID"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendToERPWorkflowInfo(ByVal containerName As String, ByVal employeeID As String, ByRef msg As String) As Boolean
        Try
            Dim strSql = New StringBuilder
            strSql.AppendFormat(<string>
                                SELECT DECODE(cc.containerid,NULL,c.mfgworkflowid,1,cc.bsworkflowid,cc.workflowid) workflowid,c.containerid
                                FROM container c 
                                LEFT JOIN containercurrentinfo cc ON cc.containerid = c.containerid
                                WHERE c.containername = '{0}'
                            </string>.Value, containerName)
            Dim dtContainer = DatabaseHelper.Query_ReturnDatTable(strSql.ToString())
            '查询是否维护了商品工时
            strSql.Clear()
            strSql.AppendFormat("SELECT ws.commoditytime FROM workflowstep ws WHERE ws.workflowid = '{0}'", dtContainer.Rows(0)("workflowid").ToString())
            Dim dtStep = DatabaseHelper.Query_ReturnDatTable(strSql.ToString())
            '如果工艺的工序维护了商品工时，则插入
            If dtStep.AsEnumerable().Where(Function(p) Not String.IsNullOrWhiteSpace(p("commoditytime").ToString())).Count > 0 Then
                '插入推送ERP中间表
                strSql.Clear()
                strSql.AppendLine("insert into SendToErpWorkflowInfo sw (sw.operatype,sw.containerid,sw.workflowid,sw.issenderp,sw.sendtoerpworkflowinfoid,sw.createdate,sw.createempid) values (")
                strSql.AppendLine("'热表商品工时',")
                strSql.AppendFormat("'{0}',", dtContainer.Rows(0)("containerid").ToString())
                strSql.AppendFormat("'{0}',", dtContainer.Rows(0)("workflowid").ToString())
                strSql.AppendLine("0,")
                strSql.AppendLine("sys_guid(),")
                strSql.AppendLine("sysdate,")
                strSql.AppendFormat("'{0}')", employeeID)
                DatabaseHelper.ExecuteSql(strSql.ToString())
                Return True
            Else
                msg = "未维护商品工时"
                Return False
            End If
        Catch ex As Exception
            msg = "报错：" + ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 获取工艺以及工艺下的工序是否存在附件
    ''' </summary>
    ''' <param name="workflowName"></param>
    ''' <param name="workflowRev"></param>
    ''' <remarks></remarks>
    Public Function IsExistsWorkflowDocInfo(ByVal workflowName As String, ByVal workflowRev As String) As Boolean
        Dim result = False
        Dim sql = String.Format(<string>
                    SELECT w.workflowid --,s.specid 
                    FROM workflow w 
                    LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                    --LEFT JOIN workflowstep ws ON ws.workflowid = w.workflowid
                    --LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                    --LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                    WHERE wb.workflowname = '{0}' AND w.workflowrevision = '{1}' 
                  </string>.Value, workflowName, workflowRev)
        Dim workflow = DatabaseHelper.Query_ReturnDatTable(sql)
        If workflow.Rows.Count > 0 Then
            '先判断工艺是否有附件
            Dim dtw As DataTable = m_DocViewObj.GetObjectDoc("Workflow", workflow.Rows(0)("workflowid"))
            For Each drw As DataRow In dtw.Rows
                Dim remoteUrl = mesDocPath + HttpUtility.UrlEncode(drw("FilePath").ToString().Replace("Temp/", "")).Replace("+", "%20")
                Dim req As WebRequest = WebRequest.Create(remoteUrl)
                Try
                    Dim res As WebResponse = req.GetResponse() '如果没有该文件，会报错404，所以用try捕捉
                    If res.ContentLength > 0 Then
                        res.Close()
                        result = True
                    Else
                        res.Close()
                        result = False
                        Exit For
                    End If
                Catch ex As Exception
                    result = False
                    Exit For
                End Try
            Next
            'If result Then '工艺验证没问题，则验证工序
            '    For Each dr As DataRow In workflow.Rows '循环每个工序
            '        Dim dts As DataTable = m_DocViewObj.GetObjectDoc("Spec", dr("specid")) '查询工序的所有附件
            '        If dts.Rows.Count > 0 Then
            '            For Each drs As DataRow In dts.Rows '循环工序的每个附件
            '                Dim remoteUrl1 = mesDocPath + HttpUtility.UrlEncode(drs("FilePath").ToString().Replace("Temp/", "")).Replace("+", "%20")
            '                Dim req1 As WebRequest = WebRequest.Create(remoteUrl1)
            '                Try
            '                    Dim res1 As WebResponse = req1.GetResponse() '如果没有该文件，会报错404，所以用try捕捉
            '                    If res1.ContentLength > 0 Then
            '                        result = True
            '                        res1.Close()
            '                    Else
            '                        res1.Close()
            '                        result = False
            '                        Exit For
            '                    End If
            '                Catch ex As Exception
            '                    result = False
            '                    Exit For
            '                End Try
            '            Next
            '            '如果工序附件不存在，则其他工序不用再判断了
            '            If Not result Then
            '                Exit For
            '            End If
            '        Else
            '            result = False '如果有一个工序的附件不存在，直接退出
            '            Exit For
            '        End If
            '    Next
            'End If
        End If
        Return result
    End Function

#Region "验证特殊过程工序"
    ''' <summary>
    ''' 验证特殊过程工序（下发时验证）add by zhangrj 20250704
    ''' </summary>
    ''' <param name="containerID">需要验证的批次ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ValidSpecialCourseSpecReport(ByVal containerID As String) As SpecialReportValidDTO
        Dim reportDto As New SpecialReportValidDTO
        Dim strSql As String = <string>
                SELECT wm_concat(r.resourcename) resourcenames,wm_concat(e.employeename) employeenames,c.containername,ws.workflowstepname,ws.workflowstepid
                FROM apsplandetail2 ap
                LEFT JOIN container c ON c.containerid = ap.containerid
                LEFT JOIN workflowstep ws ON ws.workflowstepid = ap.workflowstepid
                LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                LEFT JOIN spec s ON s.specid = nvl(sb.revofrcdid,ws.specid)
                LEFT JOIN resourcedef r ON r.resourceid = ap.resourceid AND NVL(r.isvirtual, '0') = '0'--排除掉虚拟设备
                LEFT JOIN employeeresourcelist er ON er.resourcelistid = r.resourceid
                LEFT JOIN employee e ON e.employeeid = er.employeeid
                WHERE ap.containerid = '{0}' AND ap.isuse = 0 AND s.isspotcheck = 1
                AND NOT EXISTS(SELECT 1 FROM specialreportunlimitedinfo sr WHERE sr.containerid = ap.containerid AND sr.workflowstepid = ap.workflowstepid AND sr.status = 0)
                GROUP BY c.containername,ws.workflowstepname,ws.workflowstepid</string>.Value '查询SQL
        '查询该批次的所有工序排产数据进行验证
        '如果为空, 则表示该工序不是特殊过程工序或者报工已撤销或者是无限制工序, 则不验证
        Dim dtSpecial As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerID))
        For Each drRes As DataRow In dtSpecial.Rows
            If String.IsNullOrWhiteSpace(drRes("resourcenames").ToString()) Then
                reportDto.IsValid = False
                reportDto.Message = String.Format("批次：{0}的工序：{1}下的工步无有效的设备，请联系技术人员处理！", drRes("containername").ToString(), drRes("workflowstepname").ToString())
                Exit For
            End If
            If String.IsNullOrWhiteSpace(drRes("employeenames").ToString()) Then
                reportDto.IsValid = False
                reportDto.Message = String.Format("批次：{0}的工序：{1}下的工步无设备人员，请联系技术人员处理！", drRes("containername").ToString(), drRes("workflowstepname").ToString())
                Exit For
            End If
            '开始验证该工序报工是否有效
            reportDto = IsValidSpecSpecialReport(drRes)
            If Not reportDto.IsValid Then
                Exit For
            End If
        Next
        Return reportDto
    End Function

    ''' <summary>
    ''' 判断工序是否有效的特殊过程
    ''' </summary>
    ''' <param name="drSpec"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsValidSpecSpecialReport(ByVal drSpec As DataRow) As SpecialReportValidDTO
        Dim reportDto As New SpecialReportValidDTO
        Dim strSql As String = String.Format(<string>
                SELECT ssr.indate,'' period,ssr.resourcenames,ssr.employeenames,ssr.specialcoursespecreportno
                FROM SpecialCourseSpecMapp scs 
                LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid
                WHERE scs.workflowstepid = '{0}'</string>.Value, drSpec("workflowstepid").ToString()) '查询SQL
        Dim dtReport As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql) '报告表
        If dtReport.Rows.Count = 0 Then
            reportDto.IsValid = False
            reportDto.Message = String.Format("批次：{0}的工序：{1} 没有特殊过程报告，请联系工艺技术人员处理！", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
            Return reportDto
        End If
        '
        '****开始循环判断每一个报告的有效性(每一个报告都必须有效)
        '
        For Each drRe As DataRow In dtReport.Rows
            ''查询每个报告的数据，每个报告号可能是多个报告号连起来的（每一个都要验证）
            Dim nos As String() = drRe("specialcoursespecreportno").ToString().Split(",")
            strSql = <string>
                SELECT ssb.indate,ssb.period,ssc.resourcenames,ssc.employeenames
                FROM  specialcoursespecreportbank ssb
                LEFT JOIN specialcoursespeccontent ssc ON ssc.specialcoursespecreportbankid = ssb.specialcoursespecreportbankid
                {0}
                                   </string>.Value
            Dim dtNewReport As New DataTable '最终要验证的数据
            For Each no As String In nos
                Dim sqlWhere = String.Format("WHERE ssc.code LIKE '{0}%'", no) '查询条件
                Dim dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, sqlWhere)) '得到新的报告数据
                If dtRes.Rows.Count = 0 Then
                    sqlWhere = String.Format("WHERE ssb.specialcoursespecreportno LIKE '{0}%'", no) '查询条件
                    dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, sqlWhere)) '得到新的报告数据
                End If
                dtNewReport.Merge(dtRes)
            Next
            '
            '****开始做验证*********
            '
            If dtNewReport.Rows.Count > 0 Then '有新的报告数据，则验证该数据
                reportDto = IsValidSpecialReport(drSpec, dtNewReport)
            Else
                '如果不存在新的报告数据，则验证自身即可
                Dim dtCopy = dtReport.Clone()
                dtCopy.Rows.Add(drRe.ItemArray)
                reportDto = IsValidSpecialReport(drSpec, dtCopy)
            End If
            '如果无效了，则跳出外层循环
            If Not reportDto.IsValid Then
                Exit For
            End If
        Next
        Return reportDto
    End Function

    ''' <summary>
    ''' 判断该工序的该报告是否有效
    ''' </summary>
    ''' <param name="drSpec">工序</param>
    ''' <param name="dtReport">报告列表</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsValidSpecialReport(ByVal drSpec As DataRow, ByVal dtReport As DataTable) As SpecialReportValidDTO
        Dim reportDto As New SpecialReportValidDTO
        For Each drReport As DataRow In dtReport.Rows
            '判断是否没有有效期，如果两个表的有效期都是空，则提示
            If String.IsNullOrWhiteSpace(drReport("indate").ToString) Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 存在没有有效期的特殊过程报告，请联系工艺技术人员处理！", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
                reportDto.IsValid = False
                Exit For
            End If
            '-----获取有效期
            Dim inDate As Date = Date.Parse(drReport("indate").ToString) '有效期
            If Not String.IsNullOrWhiteSpace(drReport("period").ToString) Then '如果存在年数不为空，则要将有效期累加年数
                Dim period = 0 '年数
                Integer.TryParse(drReport("period").ToString.TrimEnd("年"), period)
                inDate = inDate.AddYears(period)
            End If
            '判断有效期是否过期
            If inDate < DateTime.Now Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 存在已过期的特殊过程报告，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
                reportDto.IsValid = False
                Exit For
            End If
            '如果有效期低于5天过期
            If inDate < Date.Now.AddDays(5) Then
                reportDto.IsUnder95Day = True '热表由95天改为5天
                reportDto.IsValid = False '热表验证不通过
                reportDto.Message = String.Format("批次：{0}的工序：{1} 有效期已低于5天后过期，请知悉。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
                Exit For
            End If
            '判断是否包含人员
            Dim emps = drSpec("employeenames").ToString().Split(",")
            Dim empNoCon = New StringBuilder
            For Each emp As String In emps
                If Not drReport("employeenames").ToString.Contains(emp) Then '如果不包含该人员，则提示
                    empNoCon.AppendFormat("{0},", emp) '不包含的人员全部提示出来
                    reportDto.IsValid = False
                End If
            Next
            If Not reportDto.IsValid Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 的特殊过程报告未包含（编号为：{2}）的人员，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString(), empNoCon.ToString())
                Exit For
            End If
            ''判断是否包含某个设备
            'If Not drReport("resourcenames").ToString.Contains(drSpec("resourcenames").ToString) Then
            '    reportDto.Message = String.Format("批次：{0}的工序：{1} 的特殊过程报告未包含（编号为：{2}）的某个设备，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString(), drSpec("resourcenames").ToString)
            '    reportDto.IsValid = False
            '    Exit For
            'End If
        Next
        Return reportDto
    End Function
#End Region

    ''' <summary>
    ''' 插入到推送数据中心记录表 add by zhangrj 20250731
    ''' </summary>
    ''' <param name="dt">数据源</param>
    ''' <param name="tableName">表名</param>
    ''' <param name="uniquecolname">表唯一列名</param>
    ''' <param name="type">操作类型</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertTableChangeRecord_RB(ByVal dt As DataTable, ByVal tableName As String, ByVal uniquecolname As String, ByVal type As Integer) As ArrayList
        tableName = tableName.ToUpper()
        uniquecolname = uniquecolname.ToUpper()
        Dim insertSql = "INSERT INTO sync_tablechangerecord_rb (id,tablename,uniquecolname,uniquecolvalue,createtime,operatetype,ismeshandle) values('{0}','{1}','{2}','{3}',SYSDATE,{4},0)" '插入sql
        Dim updateSql = "UPDATE sync_tablechangerecord_rb st SET st.ismeshandle = 0 WHERE st.id = '{0}'" '更新其为未推送sql
        Dim tableInsertSql = "INSERT INTO sync_tableinfo(tablename,uniquecolname) VALUES('{0}','{1}')"
        Dim arrList = New ArrayList()
        '如果没有创建表结构，则创建
        If DatabaseHelper.GetSingle(String.Format("SELECT 1 FROM sync_tableinfo st WHERE st.tablename = '{0}'", tableName)) Is Nothing Then
            arrList.Add(String.Format(tableInsertSql, tableName, uniquecolname))
        End If
        For Each row As DataRow In dt.Rows
            If String.IsNullOrWhiteSpace(row(uniquecolname).ToString()) Then '如果表唯一列没有值，跳过不推送
                Continue For
            End If
            Dim tablechangerecord = DatabaseHelper.Query_ReturnDatTable(String.Format("SELECT ID FROM sync_tablechangerecord_rb WHERE uniquecolvalue = '{0}' AND tablename='{1}' AND operatetype = {2}", row(uniquecolname), tableName, type))
            If tablechangerecord.Rows.Count = 0 Then
                arrList.Add(String.Format(insertSql, Guid.NewGuid().ToString(), tableName, uniquecolname, row(uniquecolname).ToString(), type))
            Else
                arrList.Add(String.Format(updateSql, tablechangerecord.Rows(0)("ID").ToString())) '如果存在，则将其更新为未推送，让其再推一次（解决推送过，但是目标系统未收到的问题。）
            End If
        Next
        Return arrList
    End Function
End Class
